代码之家 › 专栏 › 技术社区 › Jakob Gade

用T-SQL从字符串中提取多个数值

sql-server-2008 tsql sql-server

Jakob Gade · 技术社区 · 14 年前

我有一列特殊格式的数据。例子:

L100000
L50
L5
S10
S15L10
S20
S90
S10
S10L5
S10L40
S10L5

该值由S和/或L组成,每个字母后面都有一个数字。我需要写一个查询,它将返回两列,S和L,这将只有相应的数字值后面的字母。
上面的例子应该是这样的:

S         L  
========  ==========
0         100000
0         50
0         5
10        0
15        10
20        0
90        0
10        0
10        5
10        40
10        5

如果未找到“S”或“L”,则默认值为零。

4 回复 | 直到 14 年前

KM. 14 年前

试试这个:

DECLARE @YourTable table (RowValue varchar(30))

INSERT INTO @YourTable VALUES ('L100000')
INSERT INTO @YourTable VALUES ('L50')
INSERT INTO @YourTable VALUES ('L5')
INSERT INTO @YourTable VALUES ('S10')
INSERT INTO @YourTable VALUES ('S15L10')
INSERT INTO @YourTable VALUES ('S20')
INSERT INTO @YourTable VALUES ('S90')
INSERT INTO @YourTable VALUES ('S10')
INSERT INTO @YourTable VALUES ('S10L5')
INSERT INTO @YourTable VALUES ('S10L40')
INSERT INTO @YourTable VALUES ('S10L5')

SELECT
    CASE
        WHEN LocationS>0 AND LocationL=0 THEN RIGHT(RowValue,Length-1)
        WHEN LocationS>0 THEN SUBSTRING(RowValue,2,LocationL-2)
        ELSE NULL
    END AS S
    ,CASE
         WHEN LocationS=0 AND LocationL>0 THEN RIGHT(RowValue,Length-1)
         WHEN LocationS>0 AND LocationL>0 THEN RIGHT(RowValue,Length-LocationL)
         ELSE NULL
     END AS L
    ,RowValue
    FROM (SELECT
              RowValue
                  ,CHARINDEX('S',RowValue) AS LocationS
                  ,CHARINDEX('L',RowValue) AS LocationL
                  ,LEN(RowValue) AS Length
              FROM @YourTable
         ) dt

输出

S                              L                              RowValue
------------------------------ ------------------------------ --------------
NULL                           100000                         L100000
NULL                           50                             L50
NULL                           5                              L5
10                             NULL                           S10
15                             10                             S15L10
20                             NULL                           S20
90                             NULL                           S90
10                             NULL                           S10
10                             5                              S10L5
10                             40                             S10L40
10                             5                              S10L5

(11 row(s) affected)

如果您有大量的数据,请尝试一下,它可能会更快(具有相同的输出,基本上删除了派生表,并使所有内容都使用内联函数):

SELECT
    CASE
        WHEN CHARINDEX('S',RowValue)>0 AND CHARINDEX('L',RowValue)=0 THEN RIGHT(RowValue,LEN(RowValue)-1)
        WHEN CHARINDEX('S',RowValue)>0 THEN SUBSTRING(RowValue,2,CHARINDEX('L',RowValue)-2)
        ELSE NULL
    END AS S
    ,CASE
         WHEN CHARINDEX('S',RowValue)=0 AND CHARINDEX('L',RowValue)>0 THEN RIGHT(RowValue,LEN(RowValue)-1)
         WHEN CHARINDEX('S',RowValue)>0 AND CHARINDEX('L',RowValue)>0 THEN RIGHT(RowValue,LEN(RowValue)-CHARINDEX('L',RowValue))
         ELSE NULL
     END AS L
    ,RowValue
    FROM @YourTable

ktharsis 14 年前

SELECT 
SVal = CASE 
  WHEN PATINDEX('S%L%', TextVal) > 0 THEN REPLACE(LEFT(TextVal, CHARINDEX('L', TextVal) - 1), 'S', '')
  WHEN PATINDEX('S%', TextVal) > 0 THEN REPLACE(TextVal, 'S', '')
  ELSE '0'
END,
LVal = CASE
  WHEN PATINDEX('S%L%', TextVal) > 0 THEN REPLACE(RIGHT(TextVal, LEN(TextVal) - CHARINDEX('L', TextVal)), 'L', '')
  WHEN PATINDEX('L%', TextVal) > 0 THEN REPLACE(TextVal, 'L', '')
  ELSE '0'
END
FROM StringList

假设S总是在L之前。此外,您可能希望将结果强制转换为数字(现在它们是字符串),具体取决于输出所需的内容。

Ray 14 年前

我建议使用一个基于clr的函数,它将使用正则表达式从字符串中提取S或L值。你可以这样使用它:

insert new_table (s_value, l_value)
  select getValue('S', original_value), getValue('L', original_value)
    from original_table

mjallday 14 年前

select
    case when charindex(data, 's') <> 0 then
        substr(data, charindex(data, 's'), charindex(data ,'l'))
    else 0 end
    , case when charindex(data, 'l') <> 0 then
        substr(data, charindex(data, 'l'))
    else 0 end
from some_table