We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Extract All Numbers from a String in SQL

Submitted by: Hannes du Preez(ojdupreez1978@gmail.com)

Monday Jan 8th 2018 by Hannes du Preez

Language: SQL, Level: Intermediate: - Learn this easy way to extract numbers from a string.

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'
SET @Pos = PATINDEX('%[^0-9]%', @strNumbers) --Find first character
WHILE (@Pos > 0)
    -- Replace alphabet with empty string.
    SET @strNumbers = STUFF(@strNumbers, @Pos, 1, '')
    -- Find next alphabet
    SET @Pos = PATINDEX('%[^0-9]%', @strNumbers)
SELECT @strNumbers [Output]
Mobile Site | Full Site
Copyright 2018 © QuinStreet Inc. All Rights Reserved