Select PART of a SQL field between two known string elements

Question

I am looking to extract only a portion of a string from a comment field in a SQL table. The current string looks like this: "TN:mba trucking|HR:cf82267|TR:solomon|AI:|N/A". What I want to do is to select anything from TN: up until the next pipe. Then I want to select anything from HR: up until the next pipe. Note, the data between these parameters are not always the same length, therefore cannot use SUBSTRING.

Which RDBMS please. For Sql-Server, see [here](http://stackoverflow.com/questions/2647/split-string-in-sql) — StuartLC, Jan 30 '14 at 08:30
For Oracle take a look at [instr](http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm) — Streuner, Jan 30 '14 at 08:31
see http://stackoverflow.com/questions/21340159/ms-sql-split-by-based-on-data-as-a-column-name/21340518 — FrankPl, Jan 30 '14 at 08:32

sameh.q · Answer 1 · 2014-01-30T10:32:37.887

For example:

I wrote this code to get me the list of numbers inside a string

SELECT DISTINCT A.REZ FROM
(
    SELECT REGEXP_SUBSTR('1213-1201+1202+1203+1204+1205+1206+1207+1208+1209+1210+1211', '[0-9]+', 1, LEVEL) AS REZ FROM dual
    CONNECT BY REGEXP_SUBSTR('1213-1201+1202+1203+1204+1205+1206+1207+1208+1209+1210+1211', '[0-9]+', 1, LEVEL) IS NOT NULL
) A;

you can write your regular expression to get the words from between each pipe

so, it will be something like this:

SELECT A.REZ FROM
(
    SELECT REGEXP_SUBSTR(Your_Var, Your_Regular_Expression, 1, LEVEL) AS REZ FROM dual
    CONNECT BY REGEXP_SUBSTR(Your_Var, Your_Regular_Expression, 1, LEVEL) IS NOT NULL
) A;

Where Your_Var will hold the text you want to work with

And Your_Regular_Expression will be the Regular Expression that you need to construct to meet your needs

Note: this is Oracle Sql Syntax

score 0 · Answer 2 · answered Jan 30 '14 at 09:13

For Oracle you could use instr and substr, to be flexible according to different stringlength. In my example I did not create a table where I could select your samplestring from. Therefore it's a bit clunky. If you can use normal fieldnames instead of repeating the string it will look much more compact.

SELECT SUBSTR('TN:mba trucking|HR:cf82267|TR:solomon|AI:|N/A',
          INSTR('TN:mba trucking|HR:cf82267|TR:solomon|AI:|N/A', 'TN:'),
          INSTR('TN:mba trucking|HR:cf82267|TR:solomon|AI:|N/A', '|') - 1)
  FROM dual;

I included the -1 to cut the pipe off. If you want to, you can cut the TN: off too. This is a very basic way and not the best solution. You will get problems, if the text contains some of the keywords you are looking for in the instr-function. Depending on the overall context it may be wise to use some regex, like simsim mentioned before.

Select PART of a SQL field between two known string elements

2 Answers2