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 numbers that have been imported from a DB2 system
‘-2.e-002’
How can I convert them into normal numbers for use with decimal datatype?
Many Thanks
select cast(-2.e-002 as decimal(12,2))
Hi Pinal,
I need some help on getting an integer value for a string which could be anything of varchar(90).
The string may have special characters like -/\()*&%@,.
All I need is an integer value consistently the same value everytime.
If I input as ‘ABC’ let’s say value should 100 or ‘ABCD’ output like 1001.
And if input as ‘CBA’ it should be different from ‘ABC’ so vlue will be 150.
Is there any I can achieve this in SQL server either using any system/built in or UDF?
I really appreciate your help in this regard.
Thanks
Refer this example
select data,checksum(data) from
(
select ‘abc’ as data union all
select ‘abcd’ union all
select ‘abc’
) as t
need help I have a column that is numberic stores example:2300.43 and I need to change it to a text output. I used a function but it is returning NULL! any idea? here is my function:
ALTER FUNCTION [dbo].[funcConvertMoneytoChar]
(@amount money
)
RETURNS varchar (200)
BEGIN
DECLARE @Output varchar(200)
IF
@amount is not null
SET @amount = cast(@Output as varchar(200) )
–IF @amount IS NULL
— SET @Output = NULL
ELSE
if
@amount= null SET @Output = NULL
RETURN @Output
END
select ‘$’+ [dbo].[funcConvertMoneytoChar](amount)as amount
from check1
mr.rania,
Alter Function Conversions(@Column money)
returns varchar(800)
as
Begin
Declare @Count int
Declare @returncolumn varchar(60)
set @Count =Count(*)
while(@Count>0)
Begin
set @returncolumn =cast(@Column as varchar(60))
set @count =@count-1
End
return @returncolumn
End
123.4900
123.4900
123.4900
123.4900
NULL
Result
123.49
123.49
123.49
123.49
NULL
The currency formation should be done in the front end application
i @ll,
i have a big problem. i need to convert a Int(11) value to an Int(8) in a Mysql stored procedure. how can i do that?
i become mistakes when i do like this:
DECLARE vSendungs_ID INT(11);
SET vSendungs_ID = CONVERT( INT(8), vSendungs_ID);
thanx for help!
Try this
DECLARE vSendungs_ID INT(11);
SET vSendungs_ID = CAST( vSendungs_ID as INT(8)) ;
But for mysql questions post at http://www.mysql.com
hi all,
m using db2 database,n m havin problem in converting char value to decimal value
ive tried cast(colname as Decimal)
but m gettin “data conversion or data mapping error”
ill thankful for your help in this regard.
Does the char column have any non numeric data?
Also post your questions at DB2 forums
how to allow ‘ (single cotes) in a text box for inserting into an sql db
When inserting data to a table, just double the single quotes. Also refer this to know how single quotes work in SQL Server
Hi, Mohan
You can use the sqlcommand parameters to insert data in text box to data base or simply use the string to build your query and then execute that
example:
dim str as string = “insert into table values(‘”& textbox1.text &”‘)
Regards
hi
i have a feild phone as varchar like 98+e00012
i want convert that data into numeric
What is your expected output?
Can you help me translate the following SQL which I can use in Oracle into SQL that will work in SQL Server?
select substr(to_char(r_time),1,10)||’00’ as TIME,n_id as NAMEID, count(*) as COUNT from mytab where r_time between to_date(‘2008-01-01 09:00′,’YYYY-MM-DD HH24:MI’) and SYSDATE group by substr(to_char(r_time),1,10)||’00’,n_id
Thanks for your help!
Try this
select substring(cast(r_time as varchar(20)),1,10+’00’ as TIME,n_id as NAMEID, count(*) as COUNT from mytab where r_time >=’2008-01-01 09:00′ and r_time<=getdate() group by substring(cast(r_time as varchar(20)),1,10+'00',n_id
HI
I want to transfer data from excel file to sqlsever 2005 table
What should I use???
Use Dts package to import data from excel sheet.
suhas
Hi I need to chache de format to date only, my field is in format mm/dd/yyyy hh:mm:ss, but i only want mm/dd/yyyy, how can i do this, please help me!!!
Thanks in advance
Gaby
Formation only matters only if you want to show them somewhere. If you use front end application, do formation there
Dear Gabriela,
select convert ( varchar(15), getdate(), 101)
output:
08/29/2008
IN the above query , you just have to change getdate() by the column name which has date….
Hope this helps,
Imran.
Hello,
I need help with converting a number into the string (e.g. 1.00 to be ‘1’). I am working with SQL Server 2005..
Please help me!
Thanks in advance,
Amina
select cast(1.00 as int)
Hi Friend(s)
I am Learning SQL i do not know how to create and see the out put can you kindly guide me? to get the output ( i required step by step) small small tags which i can understand easily,
1. how to create
2. how to insert
3. how to select
waiting for your reply.
regards
jo
1. Create table tablename
2.Insert into tablename(fileds)values(@fielsd)
3.select * from tablename
if u any doubt sent message to my gmail.id
I need to convert the number 1 to char ’01’.
How can I do that (not using ‘0’+cast(1 as varchar) ! hehehe
tks.
Carlos Barini.
That is formation issue which should be done in a front end application
Hello carlos,
I used this script recently .. works absolutely fine.
SELECT
m.MemberId,
RIGHT(REPLICATE(‘0’, 10) + CONVERT(VARCHAR, m.MemberId), 5) AS MemberCode
FROM Member m
original Source:
Thanks,
Imran.
Dave,
i have read your blog & the site is very interesting , i got the some basic things about sql server , pivot table, Union all. Good luck
cheers
Poonkodi
Sir what is the difference Between Cast and Convert
Except these two differences
1.formating is posiible in convert but not in cast.
2.cast is a ANSI compliance but Convert is not ANSI compliance.
You may need to read this for more informations about CAST and CONVERT
Hey this is great stuff! I hope someone can assist me!
I have a table with one field being varchar(25) and output data like JOBR0001 to JOBR0010.
I would like to run a query so that the next value that shows up would be JOBR0011.
I can only generate the 11 so far but I am unable to get the JOBR as I keep getting the error cannot convert varchar to int: –
SELECT REPLACE((select max(fieldx)
from Table1 WHERE fieldx like ‘JOBR%’),’JOBR’,”)+1
Any advise?
HI BG this function work if the id should be like U mentioned
CREATE Function maxvarchar(@Column varchar(25))
returns varchar(500)
as
Begin
Declare @Count int
Declare @myCount int
Declare @returncolumn varchar(100)
Declare @intpart int
Declare @leng int
Declare @search int
declare @searchlast int
declare @lastwopart varchar(25)
declare @TotalCount int
declare @storelast varchar(25)
declare @resultlen int
declare @searchres int
declare @equalcolumn varchar(25)
set @Count =Count(@Column)
set @TotalCount= 0
set @searchlast=0
while(@Count>0)
Begin
if @Count = @TotalCount
Begin
set @search = patindex(‘%R%’,@Column)
set @returncolumn = substring(@Column,1,@search+2)
set @lastwopart = substring(@Column,@search+1,len(@Column))
set @searchlast = cast(@lastwopart as int)
set @searchlast= @searchlast+1
set @storelast =cast(@searchlast as varchar(25))
set @returncolumn =@returncolumn + @storelast
set @resultlen =len(@returncolumn)
if @resultlen != len(@Column)
Begin
set @searchres = patindex(‘%R%’,@Column)
set @equalcolumn =SUBSTRING(@returncolumn,1,@searchres)
set @equalcolumn =@equalcolumn + ‘0’
set @returncolumn = substring(@returncolumn,@searchres+1,len(@Column))
set @returncolumn =@equalcolumn + @returncolumn
End
set @Count =0
End
else
set @TotalCount =@TotalCount +1
End
return @returncolumn
End
data like this
JOBR0001
JOBR0002
JOBR0003
JOBR0013
query : select max(dbo.maxvarchar(Empid)) from BG
after query
JOBR0014
Note the length of id should be equal in all rows.
Hi Bg and one who get this Problem.
The Code which i have given earlier has been lengthy and might not be consistent Now this code will be Perfect and Consistent One.
ALTER Function maxvarchar(@Column varchar(25))
returns varchar(500)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @search int
declare @searchlast int
declare @lastwopart varchar(25)
declare @storelast varchar(25)
declare @searchres int
set @Count =Count(@Column)
set @searchlast=0
while(@Count>0)
Begin
set @search = patindex(‘%R%’,@Column)
set @returncolumn = substring(@Column,1,@search)
set @lastwopart = substring(@Column,@search+1,len(@Column))
set @searchlast = cast(@lastwopart as int)
set @searchlast= @searchlast+01
set @storelast =cast(@searchlast as varchar(25))
set @Count=0
while(len(@storelast)<4)
Begin
set @storelast ='0'+ @storelast
End
set @returncolumn = @returncolumn + @storelast
set @Count=0
End
return @returncolumn
End
data like this
JOBR0250
JOBR0002
JOBR0012
JOBR0312
JOBR4312
JOBR4399
My fuction will return the Out pur like this use this query
JOBR4400
Note:The length should be 8 in all the rows
ALTER Function maxvarchar(@Column varchar(25))
returns varchar(500)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @search int
declare @searchlast int
declare @lastwopart varchar(25)
declare @storelast varchar(25)
declare @searchres int
set @Count =Count(@Column)
set @searchlast=0
while(@Count>0)
Begin
set @search = patindex(‘%R%’,@Column)
set @returncolumn = substring(@Column,1,@search)
set @lastwopart = substring(@Column,@search+1,len(@Column))
set @searchlast = cast(@lastwopart as int)
set @searchlast= @searchlast+01
set @storelast =cast(@searchlast as varchar(25))
set @Count=0
while(len(@storelast)<4)
Begin
set @storelast ='0'+ @storelast
End
set @returncolumn = @returncolumn + @storelast
set @Count=0
End
return @returncolumn
End
data like this
JOBR0250
JOBR0002
JOBR0012
JOBR0312
JOBR4312
JOBR4399
My fuction will return the Out pur like this use this query
JOBR4400
select max(dbo.maxvarchar(Empid)) from BG
reply ur QUERY can solve in Multiple ways the Easiest way is
we can use substring tooo in place of Replace…..
select ‘J0BR’ + cast(max(cast(Replace(Empid,’JOBR’,”)as int)+ 1)as varchar)from MAXV
Data like this
JOBR0250
JOBR0002
JOBR0012
JOBR0312
JOBR4312
JOBR4399
JOBR10000
output like this
J0BR10001
max(fieldx)
should be
max(substring(fieldx,4,len(fieldx))*1)
How do I convert a string of numbers into integer?
Do you want to split the numbers and store them seperately?