asked
Extract word after white space
I have the following text > set @test=10 exec procname this is a test I want to extract the string "exec procname" The text EXEC will always be there so I am using that as my starting point I have this code but is failing because CHARINDEX(' ', @String) is looking at the first white space in the string rather than the white space after "procname" so I am getting the invalid length parameter when subtracting from CHARINDEX('exec ', @String). The amount of white spaces before the EXEC can vary so I can not use a set number of white spaces. DECLARE @String VARCHAR(500) SET @String = 'set @test=10 exec procname this is a test' SELECT SUBSTRING(@String, charindex('exec ', @String), CHARINDEX(' ', @String) - charindex('exec ', @String)) Any suggestions for how I can select the whitespace after "procname" so that I do not get the error invalid length parameter passed to substring function?
For For particular query, you have to dynamically build the string so that you can find the blank space behind the 'procname' value. If you don't, it just uses the default string value, which is the whole thing. Try this: DECLARE @String VARCHAR(500) SET @String = 'set @test=10 exec procname this is a test' SELECT SUBSTRING(@String, charindex('exec ', @String), CHARINDEX(' ',RIGHT(@string,LEN(@string)- CHARINDEX('exec ',@string)-4))+5) There are probably a number of ways to accomplish this, here is another way that I put together: DECLARE @String VARCHAR(500) SET @String = 'set @test=10 exec procname this is a test' SELECT 'exec ' + LEFT(RIGHT(@string,LEN(@string)-CHARINDEX('exec ',@string)-4),CHARINDEX(' ',RIGHT(@string,LEN(@string)-CHARINDEX('exec ',@string)-4))) Very well could be better ways. Hope that helps!
17 People are following this question.
