SQL - How to return a set of numbers after a specific word

Question

In SQL i'm trying to return the first set of numerical values after a specific word. I only want the numbers from the string after the specific word. eg: ' hello : '

for example:

hello : 123            should return 123
hello : x123           should return 123
hello : 123 456        should return 123
cake : 10              should not return anything

So far I've worked out i need to do something like -

Declare @sInitialString varchar(max)
@sInitialString = " hello hello : 123 456"
--FIND ' hello : ' within @sInitialString
-- possibly save this a new substring or string?
-- find the first numerical value after this has been found

Seems simple but from previous posts it seems more complex.

I've managed to get all numerical values to return

DECLARE @sInitialString VARCHAR(MAX)

SET @sInitialString = (SELECT UPPER(' hello hello : 123 '))
select substring(@sInitialString,patindex('%[0-9]%',@sInitialString),100)

I just seem to be missing something either in my approach or the solution. Has anyone managed to achieve this?

Perhaps should move this logic into your client, would be easier — athabaska, Dec 24 '15 at 12:13
The best choice is to encapsulate this logic into a SQL CLR function. Inside a function you can implement this logic using regular expressions or using string Split operation. — Denis Reznik, Dec 24 '15 at 12:27

score 0 · Answer 1 · answered Dec 24 '15 at 12:54

Assuming your code works to find the relevant string, you can get the first value using outer apply:

select x.nums
from (select substring(@sInitialString, patindex('%[0-9]%', @sInitialString), 100) s
     ) s outer apply
     (select (case when s.s like '% %'
                   then left(s.s, charindex(' ', s.s)
                   else s.s
              end) as nums
     ) x

I don't think your logic actually works, though, because it is not looking for hello. So, you might be looking for something more like:

select x.nums
from (select (case when @sInitialString like 'hello%[0-9]%'
                   then substring(@sInitialString, patindex('%[0-9]%', @sInitialString), 100)
              end) s
     ) s outer apply
     (select (case when s.s like '% %'
                   then left(s.s, charindex(' ', s.s))
                   else s.s
              end) as nums
     ) x;

thanks for the pointer on the logic, it's given me something to think about — WelliXL, Dec 29 '15 at 09:56

score 0 · Accepted Answer · answered Dec 24 '15 at 20:15

Try solution below. To make last PATINDEX search work I had to add a space symbol at the end. I use 2 step to make code readable, but you can convert it into single statement to use in SELECT or use CTE to achieve multiple steps.

DECLARE @sInitialString VARCHAR(MAX) = ' hello hello : retert 123'
DECLARE @sToken VARCHAR(MAX) = 'hello :'

-- Add a character at the to make search of the numeric string end work
SELECT @sInitialString += @sInitialString  + ' '
-- Find String token and save the rest of the string to the variable
SELECT @sInitialString = SUBSTRING(@sInitialString, PATINDEX('%' + @sToken + '%', @sInitialString) + LEN(@sToken), 10000) 
-- The extract string from first numeric character unitl last numeric 
SELECT @sInitialString = SUBSTRING(@sInitialString, PATINDEX('%[0-9]%', @sInitialString), PATINDEX('%[0-9][a-z !@#$%^&*(()_]%', @sInitialString) - PATINDEX('%[0-9]%', @sInitialString) + 1)
SELECT @sInitialString

SQL - How to return a set of numbers after a specific word

2 Answers2

Linked