SQL: Finding dynamic length characters in a data string

Question

I am not sure how to do this, but I have a string of data. I need to isolate a number out of the string that can vary in length. The original string also varies in length. Let me give you an example. Here is a set of the original data string:

:000000000:370765:P:000001359:::3SA70000SUPPL:3SA70000SUPPL: 
:000000000:715186816:P:000001996:::H1009671:H1009671:

For these two examples, I need 3SA70000SUPPL from the first and H1009671 from the second. How would I do this using SQL? I have heard that case statements might work, but I don't see how. Please help.

can you explain in English which part of the string you need. it is difficult to suggest anything on two examples without knowing the algorithm you used to isolate those two values. — Bulat, Sep 18 '14 at 19:47
What database are you using? And how do you know what you want, just the last delimited element? — Gordon Linoff, Sep 18 '14 at 19:47
This is a DB2 database. These aren't actually delimited. They appear to be delimited by colon but that is not the case. The original string is actually a single table field. If I can grab the last colon "delimited" entry, that would work. Another issue here is the table field has a length of 150 characters. Basically, I need either the 7th or 8th "delimited" entry from the start of the string, and that should be static, so if I can somehow count to the 7th or 8th colon and substring it out, that would work. — Drauul, Sep 18 '14 at 19:59

score 1 · Answer 1 · edited May 23 '17 at 12:21

This works in Oracle 11g:

with tbl as (
select ':000000000:370765:P:000001359:::3SA70000SUPPL:3SA70000SUPPL:' str from dual
union 
select ':000000000:715186816:P:000001996:::H1009671:H1009671:' str from dual
)
select REGEXP_SUBSTR(str, '([^:]*)(:|$)', 1, 8, NULL, 1) data
from tbl;

enter image description here

Which can be described as "look at the 8th occurrence of zero or more non-colon characters that are followed by a colon or the end of the line, and return the 1st subgroup (which is the data less the colon or end of the line).

From this post: REGEX to select nth value from a list, allowing for nulls

Sorry, just saw you are using DB2. I don't know if there is an equivalent regular expression function, but maybe it will still help.

For the fun of it: SQL Fiddle

score 0 · Answer 2 · answered Sep 18 '14 at 20:02

first substring gets the string at ::: and second substring retrieves the string starting from ::: to : declare @x varchar(1024)=':000000000:715186816:P:000001996:::H1009671:H1009671:'

declare @temp varchar(1024)= SUBSTRING(@x,patindex('%:::%', @x)+3, len(@x)) 
SELECT SUBSTRING( @temp, 0,CHARINDEX(':', @temp, 0))

SQL: Finding dynamic length characters in a data string

2 Answers2