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

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)

Quest

Best Practices, SQL Function, SQL Scripts
Previous Post
SQL SERVER – FIX : Error : msg 8115, Level 16, State 2, Line 2 – Arithmetic overflow error converting expression to data type
Next Post
SQL SERVER – SQL Joke, SQL Humor, SQL Laugh – Generic Quotes

Related Posts

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?

    Reply
  • 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

    Reply
  • 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…

    Reply
  • 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

    Reply
  • SELECT CONVERT(INT, CONVERT(CHAR(10), GETDATE(),112 ))
    SELECT CAST(CONVERT(CHAR(10), GETDATE(), 112) AS INT)

    Reply
  • –only values composed of digit
    SELECT column1 FROM table WHERE column1 not like ‘%[^0-9]%’

    Reply
  • Kranti Kumar
    May 20, 2014 7:19 pm

    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…

    Reply
    • Kranti Kumar
      May 20, 2014 8:02 pm

      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

      Reply
  • 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………..

    Reply
  • 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?

    Reply
  • 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

    Reply
  • Kaliprasanna Banerjee
    December 6, 2014 6:18 pm

    i have 3 seperate text columns containing day,month and year, how do i combine them in date format
    regards
    kpb

    Reply
  • 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

    Reply
    • 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

      Reply
  • 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://&#039; + per.DepartmentID + '’ as int),

    Reply
  • hi,
    How to convert (1500.000) + (1000.00) + 1000.000 (string value) to 2500.000 (numeric) .
    please advise.

    Thanks in Advance.

    Reply
  • 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.

    Reply
    • will you always have + symbol at the end? You can replace that with blank and then convert/cast?

      Reply
  • plz give the meaning for this

    ‘DATEADD(MINUTE,CAST(TIMEZONE AS INT),GETUTCDATE())’

    Reply
  • 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!

    Reply
  • Hi sir,

    Awaiting for your response.

    Thanks,

    Reply
  • I want to convert 1250 to 12.50 , 5 to 0.5. how to convert

    Reply
  • 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″

    Reply

Leave a Reply