question

DBANovice avatar image
DBANovice 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?
tsqlssmssubstringcharindex
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
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!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DBANovice avatar image DBANovice commented ·
Thank you very much @JohnM
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.