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.

8 comments
noraz 17 Nov 2017 at 2:04 am

Hi, i need to remove alphabets from string and return only numeric which is lower than 400000. the data in the field as below and may have duplicate data in that particular column:
A200000B
B300000
450000T
JHSDHF
B34T

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 @[email protected]_ANI+1;
END
SET @[email protected]_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 [email protected]_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!