sql server - Convert exponential to number in sql

Question

I have a large amount of card tokens (16 digits) uploaded from xml file to sql-server. The problem is I see them as expression, sample below:

3.3733E+15
3.3737E+15
3.3737E+15
3.3737E+15
3.37391E+15
3.37391E+15
3.37398E+15
3.37453E+15
3.37468E+15
3.37468E+15
3.3747E+15
3.37486E+15
3.37486E+15
3.37567E+15
3.3759E+15
3.3759E+15

Any suggestion to change them to a 16 digit number? I have tried to change the data type, but got error"Conversion failed when converting the varchar value '3.37201E+15' to data type int"

Thanks for help!

Edit:

@X.L.Ant see my code below. I create this table from another one, which is just purely inserted from xml file. Is this may cause an error because some rows are empty in column TOKEN?

 CREATE TABLE MULTICURRENCY_CHECK
    (
    TOKEN varchar(255)
    )
    /*Merges all card tokens into 1 column, as in xml they are spread across different columns*/                                
    INSERT INTO MULTICURRENCY_CHECK
    (
    TOKEN
    )
    SELECT no FROM gpstransactionsnew2
        UNION ALL
    SELECT no19 FROM gpstransactionsnew2
        UNION ALL
    SELECT no68 FROM gpstransactionsnew2
        UNION ALL
    SELECT no93 FROM gpstransactionsnew2
        UNION ALL
    SELECT no107 FROM gpstransactionsnew2
        UNION ALL
    SELECT no121 FROM gpstransactionsnew2

    SELECT REPLACE(TOKEN, 'OW1', ' ')
    FROM MULTICURRENCY_CHECK

    /*Converts exponential expression to number*/
    SELECT CONVERT(numeric(16,0), CAST(TOKEN AS FLOAT))
    FROM MULTICURRENCY_CHECK

Answer 1

Try to cast your string to float before converting it :

SELECT CONVERT(numeric(16,0), CAST(TOKEN AS FLOAT))
FROM MULTICURRENCY_CHECK

• See this fiddle.

I don't know what's the format of those numbers in your XML source, but with the data you provide, you'll end up with 33733 for instance followed by a bunch of zeroes. If you have a bigger precision in your XML, maybe you should tweak your importing settings to keep this precision instead of trying to deal with that in the DB.

EDIT:

Try testing your strings with ISNUMERIC to avoid the casting errors you're getting. Adding a raw output of your column will allow you to check which value fails to convert (i.e. converts to 0).

SELECT TOKEN, 
       CONVERT(NUMERIC(16, 0), CAST(CASE 
                                      WHEN ISNUMERIC(TOKEN) = 1
                                      THEN TOKEN 
                                      ELSE 0 
                                    END AS FLOAT)) 
FROM   MULTICURRENCY_CHECK