dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Jan 8, 2018

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Extract All Numbers from a String in SQL

In SQL you can use PATINDEX (which makes use of Regular Expressions) to extract all the numbers from within a string as shown:

DECLARE @strNumbers VARCHAR(100) = 'I was born in 1978. I am 39 years old. Hopefully I wll make it to 40'
DECLARE @Pos SMALLINT = 0
SET @Pos = PATINDEX('%[^0-9]%', @strNumbers) --Find first character
 
WHILE (@Pos > 0)
BEGIN
    -- Replace alphabet with empty string.
    SET @strNumbers = STUFF(@strNumbers, @Pos, 1, '')
   
    -- Find next alphabet
    SET @Pos = PATINDEX('%[^0-9]%', @strNumbers)
END
SELECT @strNumbers [Output]
Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date