Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
949 views
in Technique[技术] by (71.8m points)

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

I have the data below.

Original Data

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

Program B Only

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...