SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT « Journey to SQL Authority with Pinal Dave

SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT

July 7, 2007 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 52 Comments

52 Responses to “SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT”

on August 8, 2007 at 2:34 pm Swamy

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
on August 9, 2007 at 12:56 am Michael

Hi Swamy,

Try:

CONVERT(CHAR, @m,103)

The 103 indicates date format dd/mm/yy. 101 would give you American.

Michael
on September 19, 2007 at 3:51 am aswini

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
on September 19, 2007 at 10:14 pm jvchoudary

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
on October 2, 2007 at 6:34 pm pinaldave

Hi,

@aswini,

Visit http://search.SQLAuthority.com and search for list to table.

@jvchoudary,
You can use case statement.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on October 3, 2007 at 11:13 am Judie

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.
on November 27, 2007 at 10:03 pm Mithun

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.
on December 22, 2007 at 2:48 am Anand

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.
on December 27, 2007 at 7:35 am CoolDeep

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.”
on January 4, 2008 at 12:23 pm natarajan

how i convert varchar (6/7) this type into int
on January 10, 2008 at 8:20 pm patel

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?
on January 16, 2008 at 9:33 am Rao

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
on February 28, 2008 at 2:30 pm LV

hi, how do convert the follwing data into single digit? without any decimal places and zeros? thanx…

from “001.000″ to “1″ ?
on March 10, 2008 at 5:34 pm Deendayal saini pilani

hi;
natarajan u should be used as convertion of floting point expression

select convert(int,(select 8/7))
on March 16, 2008 at 1:36 am gram1138

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
on April 2, 2008 at 4:06 am Charles

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.
on April 4, 2008 at 1:51 am vipul

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
on April 9, 2008 at 12:08 pm Sam

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
on April 16, 2008 at 1:24 am rk

Vipul,

You can use replace function

replace(’398-768-6789′,’398′,’(398)’)
on May 9, 2008 at 2:25 pm innovatel

Thank you very much … I did not remember the syntax .. you help me :)

Thanks and bye :)
on May 19, 2008 at 12:03 pm preethi

is there is any sql query that,if i give “1″ as input,the output should be “one”.help me to solve this query
on June 11, 2008 at 8:51 pm Sharon Ba

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
on June 18, 2008 at 1:37 am Sharath

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
on June 19, 2008 at 9:53 am rania

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
on July 22, 2008 at 9:06 pm rice

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!
on July 23, 2008 at 10:44 am congenial

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.
on July 24, 2008 at 11:34 am mohan

how to allow ‘ (single cotes) in a text box for inserting into an sql db
on July 28, 2008 at 2:29 pm Farid Masood

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
on August 4, 2008 at 2:54 pm dharmendra rawat

hi

i have a feild phone as varchar like 98+e00012
i want convert that data into numeric
on August 6, 2008 at 12:02 am Satya

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!
on August 22, 2008 at 11:26 am Sach

HI

I want to transfer data from excel file to sqlsever 2005 table

What should I use???
on August 29, 2008 at 7:10 pm Gabriela

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
on August 30, 2008 at 11:18 am Imran Mohammed

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.
on September 8, 2008 at 4:12 pm Amina

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
on September 18, 2008 at 11:40 am A. JOSHUA

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
on September 22, 2008 at 8:50 pm Carlos Barini

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.
on September 23, 2008 at 6:16 am Imran Mohammed

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: http://sqlblogcasts.com/blogs/repieter/archive/2007/11/15/sql-how-to-fill-up-a-column.aspx

Thanks,
Imran.
on October 1, 2008 at 12:44 pm poonkodi

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
on October 4, 2008 at 10:50 am rakesh

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.
on October 7, 2008 at 11:26 pm BG

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?
on October 21, 2008 at 11:14 pm girlseafarer

How do I convert a string of numbers into integer?
on October 22, 2008 at 8:19 am Imran Mohammed

You cannot convert character to Integer. Not using CAST and Convert function.

Thanks,
Imran.
on November 14, 2008 at 3:06 am cbr600gyrl

gram1138 and Anand,
Thank you very much for the code! I had an idea on how to convert my string of numbers with varying lengths, but I didn’t quite remember all the string manipulation functions and I didn’t have enough time to research it again. But with your examples, I got it working in five minutes. Thanks!
on December 2, 2008 at 2:04 pm smita

Hello Sir,

The information there on this blog is very useful & helpful thanks for providing such informative infromation.

Sir i have a query in ms-sql server2000
can we convert numeric(18,0) to identity(1,1) which will be primary key ,
since this ID numeric(18,0) is a foreign key in another table so i wanted that this key should be as primary auto incrementing key

whereas i am also unable to do insert into statement for the same
error ocuuring is as : Cannot insert explicit value for identity column in table

Can you please help me out.
on December 5, 2008 at 8:20 am jothi

Hi,

I want to convert output values for a status cloumn which are in char and I want to be displayed as a number just like below

ex:

status: status

green 1
yellow 2
red 3

Thankx
on December 5, 2008 at 9:52 am Imran Mohammed

@Jothi,

you can use CASE function, I wrote a small example…

CREATE TABLE EXAMPLE1 ( STATUS VARCHAR(50))
GO
INSERT INTO EXAMPLE1 (STATUS) SELECT ‘GREEN’ UNION ALL
SELECT ‘YELLOW’ UNION ALL
SELECT ‘RED’
GO
SELECT * FROM EXAMPLE1
GO
SELECT CASE WHEN STATUS =’GREEN’ THEN 1
WHEN STATUS =’YELLOW’ THEN 2
WHEN STATUS = ‘RED’ THEN 3 END STATUS
FROM EXAMPLE1
GO
DROP TABLE EXAMPLE1
GO

Regards,
IM.
on December 13, 2008 at 4:26 pm swati

Please Tell me from wherei can download sql sever 2005
on December 14, 2008 at 3:12 am Imran Mohammed

@Swathi.

You can download SQL Server Express Edition for free from this link.

http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en

You can also try for evaluation copy which is valid for 180 days ( 6 months ).

check this link for more details
http://www.microsoft.com/sqlserver/2005/en/us/trial-software.aspx

Regards
IM
on December 16, 2008 at 2:22 pm Jenny

HI,
I am the beginner user of the SQL 2005.
May i know how to write a stored proc.
to convert integer into string without using CAST and CONVERT.

Any advise ?

thanks
on December 25, 2008 at 2:58 pm Asma qureshi

Hi Dave,

i m Asma Qureshi here.
when ever i search any of my query in sql server then i found the solution from your blog

yes its very nice.

Thanks

Regards:
Asma Qureshi
Software Engineer
(ASP.Net2.0/3.0 , SQL server 2005/2008, Ajax)
*************************************
United Arab Emirates
(Dubai)
on January 2, 2009 at 5:57 pm saleel

How can convert integer to time(hh:mm:ss) in sql server 2005. I am getting time as in seconds.
Please help me.
on January 2, 2009 at 6:25 pm saleel

I got solution
SELECT CONVERT(varchar, DATEADD(second, 60, 0), 108)