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?
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