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

Primary tabs

SQL, Caché

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

  • 0
  • 0
  • 678
  • 1
  • 1

Answers

Use $Piece function to return a substring identified by a delimiter. :

SELECT $PIECE('96842-2-AEV.00001-63561-001958-100728760I-42', '-', 2)

Yields 2.

Comments

That worked perfectly, thanks so much!!

Steve