Extract multiple numeric values from string with T-SQL

Question

I have a column with data in a special “format”. Example:

L100000
L50
L5
S10
S15L10
S20
S90
S10
S10L5
S10L40
S10L5

The value consists of an “S” and/or an “L”, each with a number following the letter. I need to write a query, which will return two columns, “S” and “L”, which will have only the coresponding numeric value following the letter.
The above example should look like this:

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

If no "S" or "L" is found, the default value is zero.

Is there some reason why these aren't inserted in separate columns in the first place? It would be much easier to create a computed column that puts them back together than write a function that parses and extracts the values. — tvanfosson
– tvanfosson, Commented Feb 24, 2010 at 13:06
I'm importing data from a ... let's call it "interesting" ... legacy database, where the developer chose to store multiple values in a single nvarchar column. I'm trying to clean it up, and split the data into separate columns in an import routine. — Jakob Gade
– Jakob Gade, Commented Feb 24, 2010 at 13:09

KM. · Accepted Answer · 2010-02-24 13:46:17Z

try this:

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

OUTPUT

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)

if you have loads of data give this a try, it may be faster (has same output, basically removed the derived table and made everything use inline functions):

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

I just saw the last line of the question f no "S" or "L" is found, the default value is zero. to achieve that, just change the two CASE statement's ELSE clause from ELSE NULL to ELSE 0. Also the sample data from the question didn't contain a NULL row or a row with a junk value like 'ABC', my queries will return the CASE default for both.
Excellent, your second suggestion worked "right out of the box". Thanks! :)

ktharsis · Accepted Answer · 2010-02-24 13:39:02Z

1

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

Assumes S always comes before L. Also you might want to cast the results as numbers (they are strings now) depending on what you need for the output.

answered Feb 24, 2010 at 13:39

ktharsis

3,1901 gold badge21 silver badges30 bronze badges

Comments

Ray · Accepted Answer · 2010-02-24 13:20:13Z

0

I would suggest a clr-based function which would use a regex to extract the S or L value from the string. You could use it like this:

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

answered Feb 24, 2010 at 13:20

Ray

21.9k6 gold badges51 silver badges67 bronze badges

Comments

mjallday · Accepted Answer · 2010-02-24 13:29:22Z

0

not tested but should be close assuming s always appears before l:

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

answered Feb 24, 2010 at 13:29

mjallday

10.2k9 gold badges54 silver badges73 bronze badges

1 Comment

KM. Over a year ago

Msg 195, Level 15, State 10, Line 3 'substr' is not a recognized built-in function name. and you get: Msg 174, Level 15, State 1, Line 6 The substring function requires 3 argument(s). after that.

Collectives™ on Stack Overflow

Extract multiple numeric values from string with T-SQL

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related