How to substring string value in SQL Server 2008

Question

I have the following strings

KLPI_2012_CBBE2_E_12704_2012-09-21_13_59_52
IYT_2012_CBBI1_S_66_2012-09-21_15_28_53

I want to extract everything after the 1st _ & before the 5th _

ex:

2012_CBBE2_E_12704 
2012_CBBI1_S_66

Answer 1

Method 1. Nested CHARINDEX calls.

Use CHARINDEX('_', stringvalue) to get the position of the first _ in stringvalue. You can instruct CHARINDEX to start searching from a certain position, if you pass that position as the third argument:

CHARINDEX(`_`, stringvalue, startpos)

Now, if the starting position will be the result of CHARINDEX('_', stringvalue)+1, i.e. like this:

CHARINDEX(`_`, stringvalue, CHARINDEX(`_`, stringvalue) + 1)

then that will give you the second _'s position. So, to find the fifth _, you'll need to nest CHARINDEX three more times:

WITH aTable AS (
  SELECT
    *
  FROM
    (VALUES
      ('KLPI_2012_CBBE2_E_12704_2012-09-21_13_59_52'),
      ('IYT_2012_CBBI1_S_66_2012-09-21_15_28_53')
    ) AS v (aStringColumn)
),
positions AS (
  SELECT
    aStringColumn,
    Underscore1 = CHARINDEX('_', aStringColumn),
    Underscore5 = CHARINDEX('_',
                            aStringColumn,
                            CHARINDEX('_',
                                      aStringColumn,
                                      CHARINDEX('_',
                                                aStringColumn,
                                                CHARINDEX('_',
                                                          aStringColumn,
                                                          CHARINDEX('_',
                                                                    aStringColumn
                                                                   ) + 1
                                                         ) + 1
                                               ) + 1
                                     ) + 1
                           )
  FROM
    aTable
)
SELECT
  aSubstring = SUBSTRING(aStringColumn,
                         Underscore1 + 1,
                         Underscore5 - Underscore1 - 1
                        )
FROM
  positions
;

Method 2. Character split + ranking.

1. Use a numbers table to split every string into single characters, pulling their positions along the way.

2. Rank every character's occurrence in the string.

3. Get two subsets:

   1) with the character _ and the ranking of 1;

   2) with the character _ and the ranking of 5.

   Join these subsets with each other.

4. Use the corresponding positions of _ #1 and _ #5 similarly to Method 1 to get the substring(s).

WITH aTable AS (
  SELECT
    *
  FROM
    (VALUES
      ('KLPI_2012_CBBE2_E_12704_2012-09-21_13_59_52'),
      ('IYT_2012_CBBI1_S_66_2012-09-21_15_28_53')
    ) AS v (aStringColumn)
),
split AS (
  SELECT
    t.aStringColumn,
    aChar    = SUBSTRING(t.aStringColumn, n.Number, 1),
    Position = n.Number
  FROM
    aTable t
    INNER JOIN Numbers n
      ON n.Number BETWEEN 1 AND LEN(t.aStringColumn)
),
ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY aStringColumn, aChar ORDER BY Position)
  FROM
    split
  WHERE
    aChar = '_'
)
SELECT
  aSubstring = SUBSTRING(first.aStringColumn,
                         first.Position + 1,
                         fifth.Position - first.Position - 1
                        )
FROM
  ranked first
  INNER JOIN ranked fifth
    ON first.aStringColumn = fifth.aStringColumn
WHERE
  first.rnk = 1
  AND fifth.rnk = 5
;

---

Note: both methods assume that every aStringColumn value does have at least 5 underscore characters.