Few of the questions I receive very frequently. I have collect them in spreadsheet and try to answer them frequently.
How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table
Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.
Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.
How to parse/retrieve only numbers from column values contains alpha-numeric characters?
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
What are the online references for CAST and CONVERT?
CAST and CONVERT
Reference : Pinal Dave (https://blog.sqlauthority.com)
439 Comments. Leave new
Hi,
I have datas like the following and would like to convert them to date format.
1177091222608
1178856000000
How do I do this please. The following is the script that I wrote. I am unable to proceed further.
DECLARE @m bigint, @n int, @k int
SET @m =1178856000000
SET @n = CONVERT(int, @m/1000 + 1900)
SELECT @m, @n
SELECT @k = (@m%1000)
SELECT @k
How to convert varchar variable which may contain ‘aaa’ or ‘100’ we want convert when the varchar has ‘100’ in a sql statement
Hi Swamy,
Try:
CONVERT(CHAR, @m,103)
The 103 indicates date format dd/mm/yy. 101 would give you American.
Michael
Hi,
how do i convert string type like ‘ 104,116, 96’
as int data type??
i am not able to use cast or convert since…
the string has been separated by commas
remove the Commas than You can Convert Use patindex or charindex…
You may need
where ‘,’+@string+’,’ like ‘%,’+cast(col as varchar(10))+’,%’
@Madhivanan thanks alot
Hi Madhivanan,
Can you please elaborate it in detail
hi,
i have a doubt ,i want to convert months into numbers i.e,
jan as 1,feb as 2 ….is it possible using cast and convert func
declare @month varchar(12)
set @month=’jan’
select month(cast(@month+’ 2000′ as datetime))
Hi, I’m importing text data. I have
“24.25-”
“19.00”
etc.
How do you convert text data to numeric with the sign? I need it to look like this.
-24.25
19.00
etc.
I’m using this:
CASE WHEN ISNUMERIC(Hours)= 1 THEN CAST(Hours AS NUMERIC) ELSE 0 END AS Hours
It doesn’t think “24.25-” is numeric so it’s setting it to 0.
thank you.
Hi Judie this code will work for You use this User Defined
alter Function Signs(@Column text)
returns numeric(4,2)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @leng int
Declare @search int
set @Count =Count(*)
while(@Count>0)
Begin
set @returncolumn = cast(@Column as varchar(100))
set @search = patindex(‘%-%’,@Column)
if @search != 0
Begin
set @leng =len(@returncolumn)
set @returncolumn = substring(@returncolumn,1,@search-1)
set @returncolumn = ‘-‘+ @returncolumn
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
Else
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
return @returncolumn
End
Before using this Function
24.25-
19.00
after this using function
-24.25
19.00
After
Hi Judie this code will work for You use this User Defined
alter Function Signs(@Column text)
returns numeric(4,2)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @leng int
Declare @search int
set @Count =Count(*)
while(@Count>0)
Begin
set @returncolumn = cast(@Column as varchar(100))
set @search = patindex(‘%-%’,@Column)
if @search != 0
Begin
set @leng =len(@returncolumn)
set @returncolumn = substring(@returncolumn,1,@search-1)
set @returncolumn = ‘-’+ @returncolumn
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
Else
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
return @returncolumn
End
Before
24.25-
19.00
after using this function
-24.25
19.00
After
Always use proper datatype to store data
select case when col like ‘%-‘ then left(col,len(col)-1)*-1 else col*1 end from your_table
I have a similar problem as aswini has or had. My query looks like this.
declare @site as varchar(20)
set @site = ‘1,2,3,4,5,6,7,8,9’
select count(*) as numbersof from tassmnt a
where cast(a.site as varchar) in (@site)
here site column is integer datatype in sql database. so i changed it to varchar. This query returns “zero” records to me. So what modification has to be done to the above query to get my desired result.
select count(*) as numbersof from tassmnt a
where ‘,’+@site+’,’ like ‘%,’+cast(a.site as varchar(10)) +’,%’
Thanks alot from Nikhil Soni, Amrinder Pandher
can anyone tel me how to generate dynamic reports in SSRS?
What did you mean by it? Do you want to build a report based on the output from a Dynamic sql?
Hi Mithun/aswini,
we cannot compare list of values as string against int. to compare you need to create temp table and split string into rows and use IN clause in SELECT statement.
declare @site as varchar(20)
set @site = ‘1,2,3,4,5,6,7,8,9’
declare @t1 table
(
t1 int
)
while charindex(‘,’,@site) > 0
begin
insert into @t1 select substring(@site,1,1)
SET @site = substring(@site,3,len(@site))
end
Select * from table1
where site in ( select * from @t1)
cheers,
anand.
Hi
Hi Anand,
insert into @t1 select substring(@site,1,1)
line will have problem if CSV contains 2 digit no.
i.e ‘15,1,2,3’
it will give following error on 15 in csv
“Conversion failed when converting the varchar value ‘,’ to data type int.”
how i convert varchar (6/7) this type into int
How do you want it to be sored in int column?
how do i convert a variable which is INT to varChar? or vice versa.
-considering the varChar variable can only be months of the year which user selects when the stored procedure runs?
I want this detail
Use CAST or CONVERT function
Hi,
I have following scenario and highly appreciated your help.
I have 2 tables table1,table2 with columns table1.Date_opened,table2.date_open,Both the columns are Varchar(25).
I need to check a condition
1)if table1.date_opened=null then table2.date_open should be as ResultDate.
2) The ResultDate is paramater for my query to display the results where ResultDate is between(@fromdate and @Todate,which are of type Datetime.
Thanks in advance,
Rao
use
coalesce(table1.date_opened,table2.date_open)
hi, how do convert the follwing data into single digit? without any decimal places and zeros? thanx…
from “001.000” to “1” ?
Most likely you may need
select cast(001.000 as int)
hi;
natarajan u should be used as convertion of floting point expression
select convert(int,(select 8/7))
To avoid the error that CoolDeep encountered when the string contains numbers of varying length, try this change from Anand’s suggestion
declare @site as varchar(2000)
set @site = ‘123,234,34567,45678,567890,6789012,701,80,9’
declare @t1 table
(
t1 int
)
while charindex(‘,’,@site) > 0
begin
insert into @t1 select substring(@site,1,(charindex(‘,’,@site)-1))
SET @site = substring(@site,charindex(‘,’,@site)+1,len(@site))
end
insert into @t1
select @site
Select * from @t1
I can’t get sp_executesql to accept more than 10 converted data types. From and after the 11th variable it repeats the converted data type of the 10th.
Alter Function Brack(@Column char(1200))
returns char(800)
as
Begin
Declare @Count int
Declare @returncolumn char(60)
Declare @leng int
set @Count =Count(*)
while(@Count>0)
Begin
set @leng = patindex(‘% %’,@Column)
set @returncolumn ='(‘ + left(@Column,@leng)+ ‘)’ + substring(@column,@leng,len(@column))
set @count =@count-1
End
return @returncolumn
End
Mr. vipul
Declare @Count int // Count No.or Rows to be counted
Declare @returncolumn char(60) //Result store purpose
Declare @leng int //Length will be stored after searching set @Count =Count(*) // rows count
while(@Count>0) //Conditional loop
Begin
set @leng = patindex(‘% %’,@Column) //space position
set @returncolumn =’(‘ + left(@Column,@leng)+ ‘)’ + substring(@column,@leng,len(@column))//will get result
set @count =@count-1//decreament one count
End
return @returncolumn //End of the Result
End
If any doubts or clarrifications please mail me this is a worked out example definitely it will work 100%
Phone Type
489 234-7654 for this type i have worked it My result would be (489) 234-7654.SImilarly you can work with your problem with minor changes
Alter Function Brack(@Column char(1200))
returns char(800)
as
Begin
Declare @Count int
Declare @returncolumn char(60)
Declare @leng int
set @Count =Count(*)
while(@Count>0)
Begin
set @leng = patindex(‘% %’,@Column)
set @returncolumn =’(‘ + left(@Column,@leng)+ ‘)’ + substring(@column,@leng-1,len(@column))
set @count =@count-1
End
return @returncolumn
End.
EX 396 476-7689 i.e (396) 476-7689
hello sir,
this is vipul
i am confused abt one query
so pls help me
i have a column name ‘phone no’
so like 398-768-6789
so i want to convert it to
(398)-768-689
SO PLS DO IT FOR ME
THANKS
Create UserDefined Function for that Using PatIndex or CharIndex.If You want to send the Entire Code U may reply me.I will send
of this Type 408 496-7223
Alter Function Brack(@Column char(1200))
returns char(800)
as
Begin
Declare @Count int
Declare @returncolumn char(60)
Declare @leng int
set @Count =Count(*)
while(@Count>0)
Begin
set @leng = patindex(‘% %’,@Column)
set @returncolumn ='(‘+ left(@Column,@leng-1)+’)’ + substring(@column,@leng,len(@column))
set @count =@count-1
End
return @returncolumn
End
If you use front end application, do formation there
Hi,
Am having a column with varchar type which have some null values in it . I want to convert it to numeric. When i use cast or convert function am getting errror
Thanx in advance
using nvl we can solve this
nvl is for ACCESS. For SQL Server use coalesce or isnull
If the value is NULL, what do you want to see it as a numeric value?
Vipul,
You can use replace function
replace(‘398-768-6789′,’398′,'(398)’)
Thank you very much … I did not remember the syntax .. you help me :)
Thanks and bye :)
is there is any sql query that,if i give “1” as input,the output should be “one”.help me to solve this query
Where do you want to show this?
Some reporting tools like Crystal Reports has this feature