· Apr 28, 2016

Pull out characters from the middle of a text string using SQL

I have to disect thousands upon thousands of strings like this:


I've been able to pull out the first, second and final segments as separated by hyphens, but now I have a predicament where I need to use the second segment, the 2 shown in the example above, in a join to another table.  Problem is Cache apparently doesn't like doing that.  Just the same, I have to find a way.

Here's the code I'm using to pull that segment out into as separate field in the query results:

substring(prov_record_id, charindex('-', prov_record_id) +1, charindex('-AEV.', prov_record_id) - charindex('-', prov_record_id) - 1)

The value can be one or two digits.  The code I'm using works perfectly, but Cache won't let me use it in a left outer join.  That data point is only available in the concatenated example shown at the top of this post in the table that string exists in.  I have to pull out both the first and segment segment (shown here as 96842-2) to successfully join to another table.  I can pull the 96842 out, but I can't get the 2.

Any help would be so very much appreciated!



Discussion (2)0
Log in or sign up to continue