Word search after a specific line - sql management studio 2012

Question

I have a text column with fields below (small sample, there are many options):

INSERT INTO #retention1 VALUES ('hello Action=Refer non-action=non-refer') INSERT INTO #retention1 VALUES ('bye Action=Follow non-action=non-refer') INSERT INTO #retention1 VALUES ('hello non-action=non-refer Action=compare') 

I need to find the word after "Action =" example ANSWER: Record 1: See Record 2: Record 3: Compare

If all the words after "Action =" were the same length, I can do this. Unfortunately, the length is unknown from all options. A word after an action almost always differs not only in the three options above.

Any suggestions for ideas would be greatly appreciated.

This is the code I used only for the "Refer" example, which works:

 BEGIN DECLARE @P_string nvarchar (100), @P_variable nvarchar (100)/*, @P_return_null nvarchar(100) = 'Y'*/ set @p_string = 'hello Action=Refer non-action=non-refer' set @p_variable = 'Action' select substring(@p_string, charindex(upper(@P_variable),upper(@P_string)) +len(@p_variable)+1,5) as trying END; 

Answer 1

The code you are looking for must first look for the string Action , and then for the space character after this word. After that, you have everything you need to control the source string.

This should work:

 DECLARE @P_string nvarchar (100), @P_variable nvarchar (100), @idx1 int, @idx2 int SET @p_string = 'hello Action=Refer non-action=non-refer' SET @p_variable = 'Action' SELECT @idx1 = charindex(lower(@P_variable),lower(@P_string)) + len(@p_variable) + 1, @idx2 = charindex(lower(' '), @P_string, @idx1) SELECT @idx1, @idx2 SELECT SUBSTRING( @p_string, @idx1, @idx2 - @idx1) as trying 

EDIT

After a more thorough analysis of the requirements, I decided to adapt the rCTE structure that I use for similar purposes. Here it is.

 CREATE TABLE #retention1 ( ID int, txt nvarchar (100) ) INSERT INTO #retention1 VALUES (1, 'hello Action=Refer non-action=non-refer') INSERT INTO #retention1 VALUES (2, 'bye Action=Follow non-action=non-refer') INSERT INTO #retention1 VALUES (3, 'hello non-action=non-refer Action=compare') ;WITH T AS ( SELECT ID, Row = 0, StartIdx = CAST(0 as int), EndIdx = CAST(0 as int), Result = CAST('' as nvarchar(max)) FROM #retention1 UNION ALL SELECT r1.ID, Row + 1, StartIdx = CAST(newstartidx AS int), EndIdx = CAST(EndIdx + newendidx as int), Result = CAST(newtoken as nvarchar(max)) FROM T JOIN #retention1 r1 ON r1.ID = T.ID CROSS APPLY( SELECT newstartidx = EndIdx + 1 ) calc1 CROSS APPLY( SELECT newtxt = substring(r1.txt, newstartidx, len(r1.txt)) ) calc2 CROSS APPLY( SELECT patidx = charindex(' ', newtxt) ) calc3 CROSS APPLY( SELECT newendidx = CASE WHEN patidx = 0 THEN len(newtxt) ELSE patidx END ) calc4 CROSS APPLY( SELECT newtoken = substring(r1.txt, newstartidx, newendidx) ) calc5 WHERE newendidx > 0 ) SELECT ID, --Result Name = left(Result, eqIdx - 1), Value = substring(Result, eqIdx + 1, len(Result) - eqIdx + 1) FROM T OUTER APPLY ( SELECT eqIdx = charindex('=', Result) ) calc6 WHERE Row != 0 AND eqIdx != 0 ORDER BY ID 

Since the table contains more than one expression for analysis, you will have problems with their reference without an identifier. So, I added an identifier to your temporary table.

The output from the CTE also contains an identifier that you can use as a reference to # retention1.ID.

Answer 2

Try the following:

 BEGIN DECLARE @ret nvarchar (100),@P_string nvarchar (100), @P_variable nvarchar (100)/*, @P_return_null nvarchar(100) = 'Y'*/ set @p_string = 'hello Action=Refer non-action=non-refer' set @p_variable = 'Action' select @ret=substring(@p_string, charindex(upper(@P_variable),upper(@P_string)) +len(@p_variable)+1,100) select substring(@ret,0,charindex(' ',lower(@ret),0)) END; 

Answer 3

String handling is plentiful with little change: the @p_string extension with space at the beginning and at the end.

 DECLARE @P_string nvarchar (100), @P_variable nvarchar (100) set @p_variable = ' Action=' set @p_string = ' hello Action=Refer non-action=non-refer ' select substring(substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)),charindex('=',substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)))+1,CHARINDEX(' ', substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)))-LEN(@P_variable)) set @p_string = ' bye Action=Follow non-action=non-refer ' select substring(substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)),charindex('=',substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)))+1,CHARINDEX(' ', substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)))-LEN(@P_variable)) set @p_string = ' hello non-action=non-refer Action=compare ' select substring(substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)),charindex('=',substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)))+1,CHARINDEX(' ', substring(@p_string, charindex(upper(@P_variable),upper(@P_string))+1, len(@p_string)-len(@p_variable)))-LEN(@P_variable))