How to get only number from string in SQL Server?

Question

I have string:

010-234-336-.

and I want to return only number like:

010234336.

Does anyone know how to format this string?

Possible duplicate of [Query to get only numbers from a string](http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) — Amr Eladawy, May 03 '17 at 05:11

score 3 · Answer 1 · edited May 23 '17 at 12:18

3

Try this

Select Replace('010-234-336-', '-', '')

In case you have other string and want to only numeric portion, then try below code.

Declare @strAlphaNumeric varchar(256) = '010-abnasd234-336-'

DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)

WHILE @intAlpha > 0
 BEGIN
    SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
 END

Select ISNULL(@strAlphaNumeric,0)

I got above solution :- Query to get only numbers from a string

edited May 23 '17 at 12:18

Community

1
1

answered May 03 '17 at 05:08

Ajay2707

5,690
6
40
58

It is still a string, you have to convert it to int – Norbert Forgacs May 03 '17 at 05:09

score 0 · Answer 2 · answered May 03 '17 at 05:09

0

SELECT CONVERT (INT,REPLACE('010-234-336-.','-',''))

answered May 03 '17 at 05:09

Norbert Forgacs

605
1
8
27

Some explaination or a link to the documentation might improve the quality of this answer further. – derM - not here for BOT dreams May 03 '17 at 10:22

score 0 · Answer 3 · edited May 25 '17 at 09:08

0

SELECT Replace('010-234-336-', '-', '')

You can also cast or convert to integer if desired too.

SELECT CAST(Replace('010-234-336-', '-', '') AS INT) as value

SELECT CONVERT(INT, Replace('010-234-336-', '-', '')) as value

edited May 25 '17 at 09:08

Ajay2707

5,690
6
40
58

answered May 03 '17 at 10:40

milltonion

43
1
9

score 0 · Answer 4 · answered Aug 11 '17 at 14:53

Here's a function I had made to let me remove all non-numeric characters from a string, but leave the result as a string. This was so I didn't lose any zeros from the beginning of the entry.

CREATE FUNCTION dbo.fnsStripToNumber
(
    @inString   varchar(500) = NULL
)
RETURNS varchar(500) AS
BEGIN

    /***********************************************************************
    Object      :   User Defined Scalar Function [dbo].[fnsStripToNumber]
    Script Date :   <date: 2016-04-20>
    Author      :   Just Me
    Description :   Remove all non-numeric characters from a string.
    Updates     :   2016-04-20 - JustMe
                        Updated the script for speed.
                        Moved the function to follow coding standards.
    Test Script :   
                    SELECT
                        ODS.dbo.fnsStripToNumber('ts45my12fnc098.     ') AS strNumber

    ***********************************************************************/

    DECLARE
        @strReturn  varchar(500) = '',
        @intEnPos   int          = 1
    --
    --  Loop through the string from beginning to end looking for any numbers.
    --
    WHILE @intEnPos > 0
    BEGIN
        --
        --  This is like a progressive update SELECT statement.
        --  The position of the next space is first found.
        --  This updated value is used in the next variable assignment to parse the
        --  string unit.
        --  The starting position for the next string unit is updated.
        --  Modify the string unit and update the return string.
        --
        SELECT
            @intEnPos = PATINDEX('%[0-9]%', @inString),
            @strReturn +=   (
                                CASE
                                    WHEN @intEnPos > 0 THEN SUBSTRING(@inString, @intEnPos, 1)
                                    ELSE ''
                                END
                            ),
            @inString = RIGHT(@inString, DATALENGTH(@inString) - @intEnPos)
    END
    --
    --  Return the proper cased string.
    --
    RETURN @strReturn

END;

GO

How to get only number from string in SQL Server?

4 Answers4