Binary Wildcard case statements?

1

Hello. I have this horrible field that has a bunch of data in it and am trying to parse out some of it. However, I am finding that some of the 'anchors' I am looking for aren't always in the right spot. Is there a way to use a wildcard instead of layers of code? Here is what I have thus far:

				When  REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,27,5)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),'')='RPPS' AND ah.FocusTellerID='6051' Then 'Bill Pay (RPPS)'
				When  REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,40,5)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),'')='RPPS' AND ah.FocusTellerID='6051' Then 'Bill Pay (RPPS)'

I tried %RPPS% with a starting field of 1 and 500 characters long. But that didn't work. Just looking for a way to identify tags in a string of characters w/o having to guess and layer.

Thanks for your help.

2

OMG. Nevermind.

When REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,1,500)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),'') Like '%RPPS%' AND ah.FocusTellerID='6051' Then 'Bill Pay (RPPS)'

3

Now that you've discovered the joy of LIKE, I'm thinking that you don't need any of the REPLACEs or the LTRIM. Also, how long is the "ah.TokenString" column?

Also, is this something that you have to determine more than once on the same set of rows? If so, consider making a persisted computed column that would return the "THEN" part of this formula so you could index it and things would be easier to find in the future.