0

I have a column which is defined as varchar. Is there any function available in sql server for having only the numeric values for the column and remove any other characters from it.

ex: 123abc -- 123

ab12c --12

ab45c? --45

Jegatheesan
  • 105
  • 2
  • 8
  • is it solved?. iam not able to add answer because topic is closed. – samir Feb 25 '20 at 13:13
  • if not i will provide my function which im using in my sp – samir Feb 25 '20 at 13:13
  • The question has been closed as a duplicate, @samir . You cannot add answers to closed questions. If you do want to contribute your answer, you would be better to on one of the marked duplicates (one of which is a highly active question). – Thom A Feb 25 '20 at 13:22

1 Answers1

0

You can try this:

DECLARE @temp TABLE
(
    string NVARCHAR(50)
)

INSERT INTO @temp (string)
VALUES 
    ('123abc'),
    ('ab12c'),
    ('ab45c?')

SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%', 
                    string) + 1) AS Number
FROM @temp
Red Devil
  • 2,343
  • 2
  • 21
  • 41