Few of the questions I receive very frequently. I have collect them in spreadsheet and try to answer them frequently. This time the questions are about CAST and CONVERT.
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
Safer Text to Number Conversions With CAST and CONVERT
The two functions do the same basic job here. CAST is the ANSI standard form, so it is easier to move to other databases. CONVERT is specific to SQL Server and takes an optional style number, which matters for dates and money, not for plain integers.
The real problem is bad data. One value like 12.5 or N/A is enough to stop the whole query with a conversion error, while an empty string quietly becomes 0. A few ways to deal with that:
- On SQL Server 2012 and later,
TRY_CASTandTRY_CONVERTreturn NULL instead of failing, so you can find the bad rows where the result is NULL but the source is not. - Be careful with
ISNUMERICon older versions. It returns 1 for values such as a lone dollar sign, a minus sign or 1e5, and those still fail when converted toINT. - A pattern check like
NOT LIKE '%[^0-9]%'finds values made only of digits. Add a check that the value is not empty as well.
Also ask why the column is text in the first place. If it always holds numbers, changing it to an INT column fixes the problem once, instead of in every query that reads it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
439 Comments. Leave new
I have a 14 digit varchar UPC code for example 00012580632101. I use either convert or case and it cuts off my leading zero digits. I need these to come out as they are relevant to the UPC number I need to use. How can I use cast or convert and keep my leading zeros?
Hi did you ever get a response or find a solution to this?
Hi everybody!
I’m pretty noobie in SQL Server, and I’m using the 2008 R2 version. I’m quite used to MSAccess and i have problems due the difference in functions names between the apps.
I have a column that holds time value, and it is a varchar. The data stored in it is like 10.58.03 and it is a time. I got how to gets only the minute [SUBSTRING(CONVERT(VARCHAR([TRN_TIME],108),4,2)] but it is a text, and I need to check if it is greater than 30, to group it.
But it is returned as String, and even addind CONVERT(INT,…) on it, it keeps returning string.
Can someone help me? Thanks in advance
How to change Interger to substring .
Example:
SELECT e.empid , e.empname, empaddress,co.CountryName,s.states,c.city,gender
FROM empdetail e INNER JOIN Country co
ON e.empid = co.Country_id JOIN state s
ON co.Country_id = s.State_id
join citys c
ON s.State_id=c.city_id
i need to apply the substring for empid..
pls any one can apply…
Hi..
I want to convert ‘1,2,3,4,5,6,7,8,9,10,11,12’ this string to numeric
when i convert it show ‘Error converting data type varchar to numeric’.
plz help
SELECT CONVERT(INT, CONVERT(CHAR(10), GETDATE(),112 ))
SELECT CAST(CONVERT(CHAR(10), GETDATE(), 112) AS INT)
which is better in this
–only values composed of digit
SELECT column1 FROM table WHERE column1 not like ‘%[^0-9]%’
Hi….
i executed below commands successfully….
create table DMART (ID int identity(1,1), PARTICULARS VARCHAR(20), QUANTITY SQL_VARIANT, RATE DECIMAL(20,1), VALUE DECIMAL (20,2))
SELECT * FROM DMART
INSERT INTO DMART (PARTICULARS, QUANTITY, RATE) VALUES(‘REDGRAM’,2.008, 70.50), (‘SUGAR’, 1.002, 35.50)
INSERT INTO DMART (PARTICULARS, QUANTITY, RATE) VALUES(‘RAVA’,1.048, 30.50), (‘CHOPPING’, 1, 139.00),
(‘BATH MESH’, 1, 21.00), (‘BELT LADIES’, 1, 99.00), (‘BRITANNIA G’, 2, 26.00), (‘BROOKE BOND’, 1, 107.00),
(‘CHICKEN MASALA’, 1, 18.00), (‘MAAZA’, 1, 28.00)
My doubt is…
unable to perform below task….
update DMART set VALUE=QUANTITY*RATE
The error is:
Msg 260, Level 16, State 3, Line 1
Disallowed implicit conversion from data type sql_variant to data type decimal, table ‘DMART’, column ‘QUANTITY’. Use the CONVERT function to run this query.
plz help me…
Hi Kranti,
you need to use convert function for updating the query.
use below query it will help you …
update DMART SET VALUE=CONVERT(DECIMAL(20,3),QUANTITY)*RATE
Hi , I want convert alphanumeric to numeric. the conversion should be like this , example input is abt538z , the output should be . 122053826(a =1 ,b=2,c=3……z=26)……….. any body tell me the code………..
Hi, Say I have one EMPLOYEE table which has one column “Date” (frmt YYYYMMDD) which is Number and another string column “REQ_DATA”. Now this “REQ_DATA” contains value which has many character including date in following frmt DD/MM/YYYY e.g. ABCDEFGDD/MM/YYYY
Is it possible any how to find out only those records from EMPLOYEE table where these two dates are equal?
Hello, I am trying to convert text into numbers. The variable “`DatabasNyaKunder$`.`Antal lgh`”. I use an excel file as data base.
The sql-query looks like below.
SELECT `DatabasNyaKunder$`.`Ordernr / offertnr`, `DatabasNyaKunder$`.Kundnr, `DatabasNyaKunder$`.Namn1, `DatabasNyaKunder$`.`Antal lgh`
Thank you, best regards Gustav
i have 3 seperate text columns containing day,month and year, how do i combine them in date format
regards
kpb
I want to get only integral part in string
for Example string contain ab123ce234fe means i want only interger part like 123234
how can i get
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR(8000)
SELECT @str = ‘ab123ce234fe’
SET @loop = 0
WHILE @loop < 26
BEGIN
SET @str = REPLACE(@str, CHAR(65 + @loop), '')
SET @loop = @loop + 1
END
SELECT @str
I am trying to create a URL from SQL (select). I have written the first part as text and the final part of the URL is taken from a column within the table. As the text is varchar. I need to convert this to ‘int’. Not sure if this is possible!
select CAST(‘<a href="http://' + per.DepartmentID + '’ as int),
hi,
How to convert (1500.000) + (1000.00) + 1000.000 (string value) to 2500.000 (numeric) .
please advise.
Thanks in Advance.
I have a varchar(50) value ‘903403+ ‘
How do I cast it in Integer? I want a SQL query which will make this value Integer without + sign.
will you always have + symbol at the end? You can replace that with blank and then convert/cast?
plz give the meaning for this
‘DATEADD(MINUTE,CAST(TIMEZONE AS INT),GETUTCDATE())’
I get Conversion failed when converting the varchar value ‘436603J’ to datatype int. LearnerID is of varchar(12). I tried CONVERT(int, LearnerID) but doesn’t work…Please help!
Hi sir,
Awaiting for your response.
Thanks,
I want to convert 1250 to 12.50 , 5 to 0.5. how to convert
Saludos desde España y gracias de antemano.
dado el resultado de un cuadro de lista:
list1=”125455,12″
como puedo pasar el valor de list1 (carácter) a cuadro de texto (text1 en formato numérico)
esto es:
text1(numérico)=list1(carácter)
resultado:
Text1=125455,12
list1=”125455,12″