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.

[cc lang=”sql”]
SELECT
CASE WHEN
ISNUMERIC(PostalCode) > 0
THEN CAST(PostalCode AS INT)
ELSE 0 END
FROM SalesLT.Address
[/cc]

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.

[cc lang=”sql”]
— 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) [/cc] 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.

7 comments
Patricia 07 Oct 2015 at 9:08 pm

Thanks! Using CAST (AS INT) worked like a charm and saved me a lot of time.

Carlos 26 Aug 2015 at 1:59 pm

BEAUTIFUL CODE!! Thank you for sharing this great example of how to use the WITH clause.

Mugun 24 Feb 2014 at 6:55 pm

Thanks, it worked like a charm.. Great to know about Numbers table!!!

SHAHNAWAZ 06 Mar 2012 at 6:41 am

ALTER PROCEDURE Proc_Filter_Mobile
— Add the parameters for the stored procedure here
@MOBSER_ID SMALLINT
AS
BEGIN
DECLARE @INT_POS SMALLINT,
@ANI NVARCHAR(20)=NULL,
@CUR_ANI NVARCHAR(1)=NULL,
@FLAG TINYINT,
@ANI_ID INT,
@ANI_SUB NVARCHAR(6)=NULL,
@COUNT TINYINT=0;

DECLARE @CUR_FILTER CURSOR;
SET NOCOUNT ON;
DECLARE @CHK_ANI TINYINT;

CREATE TABLE #Bulk_Load_Mobile_Series
(
Load_ID BIGINT IDENTITY(1,1),
MobileSeries_SeriesID int,
MobileSeries_ANI nvarchar(20)
)

— ## SETTING CURSOR ##
SET @CUR_FILTER=CURSOR FOR
SELECT DataTemp_ID,DataTemp_ANI FROM Bulk_DataTemp;

— ## OPENING CURSOR
OPEN @CUR_FILTER

–## FETCHING CURSOR VALUE
FETCH @CUR_FILTER INTO @ANI_ID,@ANI
WHILE @@FETCH_STATUS=0
BEGIN
–## CUTTING LAST FIVE CHARACTER FROM ANI
SET @ANI_SUB=SUBSTRING(@ANI,9,5);

–## CHECKING GOLDEN,SILVER AND PLATINUM NUMBER AND FILTERED
SET @INT_POS=0;
WHILE @INT_POS<=9
BEGIN
SET @CHK_ANI=1;
SET @FLAG=0;
WHILE @CHK_ANI=3)
BEGIN
SET @COUNT=1;
END;

–PRINT ‘CURRENT ANI -‘ + @CUR_ANI;
–PRINT CAST(@INT_POS AS VARCHAR);
— PRINT ‘FLAG VALUE’ + CAST(@FLAG AS VARCHAR);

END;
ELSE
BEGIN
SET @FLAG=0;
END
SET @CHK_ANI=@CHK_ANI+1;
END
SET @INT_POS=@INT_POS+1;
END

IF(@COUNT1)
BEGIN
–### DELETE THAT RECROD FROM THE TABLE###
— PRINT @ANI_ID;
INSERT INTO #Bulk_Load_Mobile_Series(MobileSeries_SeriesID,MobileSeries_ANI)
VALUES(@MOBSER_ID,@ANI);
–DELETE FROM [DBO].Bulk_DataTemp
–WHERE DataTemp_ID=@ANI_ID;
SET @FLAG=0;
SET @COUNT=0;
END
ELSE
BEGIN
SET @COUNT=0;
SET @FLAG=0;
END;
FETCH @CUR_FILTER INTO @ANI_ID,@ANI
END
CLOSE @CUR_FILTER;
DEALLOCATE @CUR_FILTER;

SELECT * FROM #Bulk_Load_Mobile_Series

END
GO

syscosys 26 Sep 2011 at 6:03 am

this is helpful. thanks!

ron stephens 26 Aug 2011 at 12:11 pm

I need to sum a field defined as varchar. In Oracle I’d do a to_num. We have an older version of SQLserver. what is the syntax of this? All the example don’t use a field name.

Derek Dieter 16 Sep 2011 at 1:36 pm

Hi Ron,

To do this, you just need to cast the value as an int. Using:

SELECT CAST(mycolumn AS int)
FROM table 1

Featured Articles

 Site Author

  • Thanks for visiting!