SQL Query to parse numbers from name

Question

The DBMS in this case is SQL Server 2012.

I need a SQL query that will grab just the numbers from a device name. I've got devices that follow a naming scheme that SHOULD look like this:

XXXnnnnn

or

XXXnnnnn-XX

Where X is a letter and n is a number which should be left padded with 0's where appropriate. However, not all of the names are properly padded in this way.

So, imagine you have a column that looks something like this:

    Name
    ----
    XXX01234
    XXX222
    XXX0390-A2
    XXX00965-A1

I need an SQL query that will return results from this example column as follows.

Anyone have any thoughts? I've tried things like casting the name first as a float and then as an int, but to be honest, I'm just not skilled enough with SQL yet to find the solution.

Any help is greatly appreciated!

Sorry, I really should have included that in the post, shouldn't I? It's MSSQL. I'll edit the post to reflect that. — Chris Marshall, Mar 26 '15 at 12:50

score 2 · Answer 1 · answered Mar 26 '15 at 12:55

SQL Server does not have great string parsing functions. For your particular example, I think a case statement might be the simplest approach:

select (case when number like '___[0-9][0-9][0-9][0-9][0-9]%'
             then substring(number, 4, 5)
             when number like '___[0-9][0-9][0-9][0-9]%'
             then '0' + substring(number, 4, 4)
             when number like '___[0-9][0-9][0-9]%'
             then '00' + substring(number, 4)
             when number like '___[0-9][0-9]%'
             then '000' + substring(number, 4, 2)
             when number like '___[0-9][0-9]%'
             then '0000' + substring(number, 4, 1)
             else '00000'
        end) as EmbeddedNumber

Dmitrij Kultasev · Answer 2 · 2015-03-26T14:31:05.290

If the first 3 chars are always needed to be removed, then you can do something like that (will work if the characters will start only after '-' sign):

DECLARE @a AS TABLE ( a VARCHAR(100) );

INSERT  INTO @a
VALUES
        ( 'XXX01234' ),
        ( 'XXX222' ),
        ( 'XXX0390-A2' ),
        ( 'XXX00965-A1' );

SELECT RIGHT('00000' + SUBSTRING(a, 4, CHARINDEX('-',a+'-')-4),5)
FROM  @a
-- OUTPUT
01234
00222
00390
00965

Another option (will extract numbers after first 3 characters):

SELECT
    RIGHT('00000' + LEFT(REPLACE(a, LEFT(a, 3), ''),
                         COALESCE(NULLIF(PATINDEX('%[^0-9]%',
                                                  REPLACE(a, LEFT(a, 3), '')),
                                         0) - 1,
                                  LEN(REPLACE(a, LEFT(a, 3), '')))), 5)
FROM
    @a;

    -- OUTPUT
    01234
    00222
    00390
    00965

Giorgi Nakeuri · Answer 3 · 2015-03-26T13:42:16.233

0

This will work even when XXX can be of different len:

DECLARE @t TABLE ( n NVARCHAR(50) )

INSERT  INTO @t
VALUES  ( 'XXXXXXX01234' ),
        ( 'XX222' ),
        ( 'X0390-A2' ),
        ( 'XXXXXXX00965-A1' )

SELECT  REPLICATE('0', 5 - LEN(n)) + n AS n
FROM    ( SELECT    SUBSTRING(n, PATINDEX('%[0-9]%', n),
                              CHARINDEX('-', n + '-') - PATINDEX('%[0-9]%', n)) AS n
          FROM      @t
        ) t

Output:

edited Mar 26 '15 at 13:42

answered Mar 26 '15 at 13:21

Giorgi Nakeuri

35,155
8
47
75

Author mentioned that he has XXXnnnnn or XXXnnnnn-XX – Giorgi Nakeuri Mar 26 '15 at 13:27

score 0 · Answer 4 · answered Mar 26 '15 at 13:31

0

This might work :

SELECT RIGHT('00000'
             + SUBSTRING(Col, 1, ISNULL(NULLIF((PATINDEX('%-%', Col)), 0) - 1, LEN(Col))), 5)
FROM   (SELECT REPLACE(YourColumn, 'XXX', '') Col
        FROM   YourTable)t

SQLFIDDLE

answered Mar 26 '15 at 13:31

Deep

3,162
1
12
21

SQL Query to parse numbers from name

4 Answers4