question

technette avatar image
technette asked

Select Specific Text

Hi! I have a description field that has the following data. How can I select all numbers after "RCVR ID" in the following text? Transfer to location 2G1/FN RCVR ID 90083-1
parsing
10 |1200 characters needed characters left characters exceeded

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

DenisT avatar image
DenisT answered
If the RCVR ID is always there, then you can use: DECLARE @string VARCHAR(200) = 'Transfer to location 2G1/FN RCVR ID 90083-1' SELECT RIGHT(@string, LEN(@string) - ( CHARINDEX('RCVR ID', @string) + 7 )); or if the number is always the same length, then simply: DECLARE @string VARCHAR(200) = 'Transfer to location 2G1/FN RCVR ID 90083-1' SELECT RIGHT(@string, 7); Basically, you need to find a common pattern in the string to look for!
10 |1200 characters needed characters left characters exceeded

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

Kev Riley avatar image
Kev Riley answered
T-SQL isn't great at string manipulation, but for one-offs, it's ok. declare @YourString varchar(50) set @YourString = 'Transfer to location 2G1/FN RCVR ID 90083-1' select substring (@YourString, patindex('%RCVR ID%', @YourString) +8, len(@YourString)) This finds the pattern 'RCVR ID' in the text and takes the substring from the start of this pattern + 8 (as that's the length of the pattern plus a space), to the end of the string
10 |1200 characters needed characters left characters exceeded

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

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.