Convert Text String to Numbers (Int)

Collected from: http://sqlserverplanet.com/tsql/convert-text-string-to-numbers-int

 

Here is a simple method to convert a text string to numbers (or an integer). This method evaluates each value in a column to determine if the datatype is numeric. If it is, then it converts it to an integer.

SELECT
    CASE WHEN
        ISNUMERIC(PostalCode) > 0
    THEN CAST(PostalCode AS INT)
    ELSE 0 END
FROM SalesLT.Address

Now, what if the column values contain text characters and you want to strip those characters out and then convert the value to an integers. Well, luckily, there is a way to do this. Most of the examples to do this will loop the values using a while loop, however this example uses a dynamic numbers table in conjunction with a trick shown to me by Simon Sabin from his blog. This method will replace all the non numeric characters and convert the values to an integer.

-- define max number of character values
-- in the string being evaluated
DECLARE @MaxNumber INT = 5000
;WITH Numbers AS
(
    SELECT 1 AS Num
    UNION ALL
    SELECT Num+1
    FROM Numbers
    WHERE Num <= @MaxNumber
)
SELECT
    CAST
    (
        (
            SELECT CASE
                WHEN SUBSTRING(PostalCode,Num,1) LIKE '[0-9]'
                THEN SUBSTRING(PostalCode,Num,1)
                ELSE '' END
            FROM Numbers
            WHERE Num <= LEN(PostalCode)
            FOR XML PATH('')
        ) AS int
     )
FROM SalesLT.Address
OPTION(MAXRECURSION 32767)

To make this work with your example, simply replace the value [PostalCode] with your field and the FROM Clause should be your table you are querying from.

Advertisements
By simplemsexchange Posted in SQL 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s