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)

, ,
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 the data like 1333200.3900 in a nvarchar column. I need to convert it into numeric…

    Reply
  • Hi guys,
    I am creating web form insert record to sqlserver, But i get a trouble like this: error: converting data type varchar to float. For handle it I try to using cast and convert. But until right now i still get trouble Incorrect syntax near ‘textbox1’. Any one can help me? Thanks.

    =========================================
    Dim typlat As Double = txtLat.Text
    Dim typlot As Double = txtLon.Text
    Dim adl As String
    adl = CSng(Val(typlat))
    adl = CSng(Val(typlot))

    strSQL = “SELECT CAST(Latitude as float) from DBREPORTCUSTOM”

    strSQL = “SELECT CONVERT(Double ‘” & typlat & “‘ ‘” & typlot & “‘) from DBREPORTCUSTOM”
    ===============================================

    Reply
  • Hi,
    I have a nvarchar value = ” Fee Component =Agency FeeÆFee=1600.00000000000000000ÆRemarks=Æ “, i want to convert the 1600.000000000 value to 1600.00 in this nvarchar value. So please suggest me the query for this.

    Thanks,
    Anki

    Reply
  • Thank Bro.
    You are a grate man. it helps me lot.
    This is my select quary in sql 2005
    SELECT EPFNo, yr, monthName, shftDate, shiftDesc, DateTim
    FROM dbo.DayOffView
    WHERE (DateTim >= CONVERT(DATETIME, ‘2012-03-15 00:00:00’, 102)) AND (DateTim <= CONVERT(DATETIME, '2012-4-17 00:00:00', 102)) AND
    (EPFNo = '2374')

    Reply
  • I have a column name WBS varchar(100) – values willl be like 27, 27.1,27.2 ,27.2.1 etc…

    now i need to get max wbs from that table, but the problem is, it is giving properly upto 0-9 it crosess like if 27.9, 27.10 then it is giving only 27.9 as max WBS? but actually max wbs is 27.10

    please give me some suggestions. its very important to me.

    this is my Query:
    (select max(wbs) from Schedule
    where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
    and EntityID = 396)

    i tried in following ways:
    (select cast(isnull(max(WBS),0)as float) as wbs from Schedule
    where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
    and EntityID = 396)

    (select Convert(float,(isnull(max(WBS),0)) as wbs from Schedule
    where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
    and EntityID = 396)

    even itried for decimal also. didnt find any result.

    Reply
    • First advise is to store these values in decimal datatype column. Try this

      select max(WBS*1.0) as wbs from Schedule
      where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
      and EntityID = 396

      Reply
      • Ya actually, intially our requirement was not like that thts y at tht time it is varchar recently it changed thts y, i tried to convert datatype to decimal bt its giving error tht error converting varchar to datatype decimal…

        i tried the thing suggested by you,its not working..
        canu please suggest another.
        Thanks

      • What did you mean by “it is not working?” Did you get error?

      • I mean , same result is getting after trying urs also…
        upto 0-9 only it taking 9 as max and giving result as 10…

      • You have multiple decimal points that can not be considered as numerals

  • SELECT TO_CHAR(TO_DATE(’10/10/10′,’YY/MM/DD’),’DD’)+MONTHS_BETWEEN(’10-OCT-10′,’10-AUG-10′)FROM DUAL;
    What will happen when above statement is executed???
    A.102 gets printed
    B. Query fails as characters cannot be added to numbers
    C. 12 gets printed
    D. MONTHS_BETWEEN function fails

    Plzzz explain

    Reply
  • i came to know ans is 12 but how ???

    Reply
  • select data.*
    /*
    ,CASE WHEN data.counselor_name IS NULL
    THEN c.combined_cem_quota
    ELSE ”
    END combined_cem_quota
    */
    ,CASE WHEN data.counselor_name IS NULL
    THEN convert(DECIMAL(10,2),c.paf_quota)
    ELSE ”
    END ‘paf_quota’
    ,CASE WHEN data.counselor_name IS NULL
    THEN convert(DECIMAL(10,2),c.heritage_quota) –c.heritage_quota
    ELSE ”
    END ‘heritage_quota’

    FROM
    (

    select l.sup_emp_code,l.sup_name,raw.counselor_name,raw.sales_contract_nbr
    ,(SUM(raw.an_oc) ) l_an_oc
    ,(SUM(raw.heritage)) l_heritage
    ,(SUM(raw.merchendise)) l_merchendise
    ,(SUM(raw.paf)) l_paf
    from
    ( select distinct sup_emp_code,sup_name from counselor_hierarchy where sup_emp_code 0 ) l
    JOIN counselor_locations c on c.emp_no = l.sup_emp_code and c.override = 1
    JOIN gor_data_raw raw on charindex(c.location_volume,raw.location_cd) > 0
    GROUP BY l.sup_emp_code, l.sup_name,raw.counselor_name,raw.sales_contract_nbr with rollup

    ) data
    JOIN counselor_locations c on c.emp_no = data.sup_emp_code
    –WHERE data.sup_emp_code IS NOT NULL

    Q. why is this program doing conversion?

    Reply
  • I have put convert function but it still giving error?

    Reply
  • define me convert function in sql

    Reply
  • I need to convert a table of 1’s and 0’s to yes and no in SQL Server 2008.
    I have no idea how to write the query. I am using a software that grabs table info from the server to update reports.

    Reply
  • Our company is converting over from an old HP3000 environment to an HP-UX environment. An ad-hoc reporting tool called DataExpress did calculations using actual characters such as “{” . We are attempting to convert those characters into SQL queries and of course we get an error: “Conversion failed when converting the nvarchar value ‘{‘ to data type int.” Has anyone ran into this before or perhaps can direct us where to look for a solution to this issue?

    Reply
  • Hello sir,

    We have a file in AS400 iseries that defined a numeric field as character filed,

    when I view it in SQL, it showed up as 885Q, but it is actually 8858-, how do

    I change this field to show up as “8858-” in SQL? Please help. Thanks

    Fannie

    Reply
  • Hi Dev how do we convert 10.2.123 to 10.2

    Reply
  • How do I convert this
    (select ‘Total Taxable:’+convert(varchar(12),sum (tax_val),)
    from [pa-svr-man01].r_flosceola.dbo.vw_OpenAccts)
    So that my answer will come back as 1,000,000,000
    Thanks

    Reply
    • I needed to be more specific my answer is coming back as 15098738203 and I want it to convert to 15,098,738,203
      Thanks

      Reply
  • Getting an error as “Error converting data type varchar to numeric.” for below statement. Can anyone suggest a way forward..?

    select CONVERT(numeric,’24,00′)

    Reply
  • I m looking for something that I can convert–
    ‘000-2.52’ should be -2.52
    ‘00002.52’ should be 2.52

    I can do the convert(decimal(11,2), ‘00002.52’ ) but for negative I cannot do, please suggest, but the data that is coming in the file, so really can’t say when the data is postive or negative.

    Please help.

    Reply
  • HARSHANA Fernando
    February 20, 2013 1:32 pm

    I have put convert function its working Thx a lot

    Reply
  • HARSHANA Fernando
    February 26, 2013 3:52 pm

    String Value Convert to INT, this query working thx a lot

    Reply
  • Hello. I require help on a conversion from varchar to numeric.
    I have nulls, 0.00, and numbers such as (134.75) to express negative values.
    They are all in varchar data type. When trying various convert/cast functions, I am still not able to convert these figures into a numeric. I’ve tried removing the parentheses but to no avail.

    Reply

Leave a Reply