Pull out characters from the middle of a text string using SQL
I have to disect thousands upon thousands of strings like this:
96842-2-AEV.00001-63561-001958-100728760I-42
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!
Thanks,
Steve
Use $Piece function to return a substring identified by a delimiter. :
Yields 2.
That worked perfectly, thanks so much!!
Steve