SQL 中的分隔函数以在分号之间拆分数据 [英] Delimited Function in SQL to Split Data between semi-colon

问题描述

我有以下数据.

我只对程序 B 感兴趣.如何使用 SQL 语法将其更改为下表?

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%'

谢谢大家的帮助.

阿迪

推荐答案

试试这个:

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 

第一个WHEN负责提取模式B[0-9][0-9]，即当B后跟两位数字时，第二个用于提取B 后跟一位数字.当未找到匹配项时，默认返回空字符串.如果您有兴趣提取模式B后跟三位数字，则需要添加另一个when(作为第一种情况)，输入模式B[-9][0-9][0-9]而不是 B[0-9][0-9] 并将最后一个数字从 4 更改为 5(提取的字符串长度).

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 返回找到匹配项的位置.

PATINDEX returns position where the match is found.

这篇关于SQL 中的分隔函数以在分号之间拆分数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！