August 22, 2007 at 9:39 am
Hi All,
For reporting purposes I need to be able to strip a portion of text out of a text field.
There is no uniform pattern to the text field, however I need to include only the text after the 2nd last ~ (if one exists that is).
Note that many fields have several ~'s in them and then there are some that don't at all.
I do have a solution to this but it is quite long winded and includes several replaces and the charindex function.
Thanks for your help.
August 22, 2007 at 9:41 am
Can you give an example of what the column data might look like?
August 22, 2007 at 9:44 am
Sure, here is a small cross section
Re Bill 177959 11/04/07
Re Bill 177959a 14/05/07
HMCS 54BNHDCQHATTONA~OP~HMCS - text written here (Re: 62637373739/89272)
HMCS #01BNHDCQpatteml~OP~HMCS - text written here (Re: 62637373739/89272)
HMCS #01BNHDCQCookeA~OP~HMCS – text written here (Re: 62637373739/89272)
August 22, 2007 at 11:43 am
OK, this is a corrected version of the query, which works with 0, 1, 2, or more ~s in the column. The select returns everything after the second to last ~ for those records where there are 2 or more ~s.
SELECT right(source,
patindex('%~%',
substring(reverse(source), patindex('%~%', reverse(source)) + 1 , len(source))) +
patindex('%~%', reverse(source)) -1 ) as result
from SourceTable
where patindex('%~%%%~%', reverse(source)) > 0
There might be an easier way...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy