sql server - Delimited Function in SQL to Split Data between semi-colon

Question

I have the data below.

I'm only interested on program B. How do I change it into the table below using SQL syntax?

Below is my syntax but it doesn't give me what I want.

SELECT
  SUBSTRING(Program, 0, CHARINDEX(';', Program)),
  SUBSTRING(
      SUBSTRING(Program, CHARINDEX(';', Program) + 1, LEN(Program)),
      0,
      CHARINDEX(';', SUBSTRING(Program, CHARINDEX(';', Program) + 1,
                               LEN(Program)))),
  REVERSE(SUBSTRING(REVERSE(Program), 0, CHARINDEX(';', REVERSE(Program)))),
  File_Count
FROM DataBase1
WHERE Program LIKE '%B%'

Thanks guys for your help.

Adhi

Answer 1

Try this:

SELECT
CASE WHEN PATINDEX('%B[0-9][0-9]%', Program)>0 THEN SUBSTRING(Program, PATINDEX('%B[0-9][0-9]%', Program) - 1, 4) 
     WHEN PATINDEX('%B[0-9]%', Program)>0 THEN SUBSTRING(Program, PATINDEX('%B[0-9]%', Program) - 1, 3) 
     ELSE '' END

FROM DataBase1 

First WHEN is responsible for extracting pattern B[0-9][0-9], i.e. when B is followed by two digits, second one is for extracting B followed by one digits. Default is returning empty string, when no match is found. If you are interested in extracting pattern B followed by three digits, you need to add another when (as the first case), enter pattern B[-9][0-9][0-9] instead of B[0-9][0-9] and change last number from 4 to 5 (length of string that is extracted).

PATINDEX returns position where the match is found.