SQL - Get numbers from string after a pound (#) sign

Question

I am attempting to get numbers from a string in SQL Server 2012 that are located after a pound (#) sign and before any spaces that follow. For instance, store numbers. Let's say we have the following:

Big Box Store #450
Big Box Store #768
Little Shop #2
Widgets Warehouse #678
Little Shop #5
Widgets Warehouse #559
Corner Boutiques #32 *CLOSED
Corner Boutiques #67 *CLOSED
Corner Boutiques #12
Buy More #1047 SUPERSTORE
1 Stop Shop #3
1 Stop Shop #17
You 2 Me #16

I would return the following: 450, 768, 2, 678, 5, 559, 32, 67, 12, 1047, 3, 17, 16.

As you can see, not all of the strings have numbers at the very end. Some of them even have a numerical character in the name of the store. I figure the best way of going about this is just to extract the numbers following the pound sign.

Is there a way to do this? I've looked at the following articles:

Query to get only numbers from a string

https://www.sqlservercentral.com/Forums/Topic456023-338-1.aspx

It seems like PATINDEX may be good to use, but I am unsure as what I've tried thus far doesn't return expected results.

Many thanks!

score 1 · Answer 1 · answered Sep 12 '17 at 15:05

Not very pretty but seems to fullfill your requirement. ;-)

DECLARE @t TABLE(
  MyString NVARCHAR(1000)
);

INSERT INTO @t VALUES 
 ('Big Box Store #450')
,('Big Box Store #768')
,('Little Shop #2')
,('Widgets Warehouse #678')
,('Little Shop #5')
,('Widgets Warehouse #559')
,('Corner Boutiques #32 *CLOSED')
,('Corner Boutiques #67 *CLOSED')
,('Corner Boutiques #12')
,('Buy More #1047 SUPERSTORE')
,('1 Stop Shop #3')
,('1 Stop Shop #17')
,('You 2 Me #16');

SELECT  MyString
       ,SUBSTRING(MyString
                 ,CHARINDEX('#', MyString) + 1
                 ,CASE(CHARINDEX(' ', SUBSTRING(MyString
                                               ,CHARINDEX('#', MyString) + 1
                                               ,LEN(MyString) - CHARINDEX('#', MyString)
                                               )
                                )
                      )
                    WHEN 0 THEN LEN(MyString) - CHARINDEX('#', MyString)
                    ELSE CHARINDEX(' ', SUBSTRING(MyString
                                                 ,CHARINDEX('#', MyString) + 1
                                                 ,LEN(MyString) - CHARINDEX('#', MyString)
                                                 )
                                  ) - 1
                  END
                 ) AS MyNumber
  FROM @t

This works although I did find sometimes the store will be listed without a number or there will not be a space between store number and a "*CLOSED" tag as predicted by scsimon. But my data type is a char(65) which works with your solution, but not with scsimon's for some reason. Thanks! — jderekc, Sep 12 '17 at 16:05

S3S · Accepted Answer · 2017-09-12T16:05:45.843

1

Another similar way... using test data from Tyron. This works even if there isn't a space after the digits.

DECLARE @t TABLE(
  MyString NVARCHAR(1000)
);

INSERT INTO @t VALUES 
 ('Big Box Store #450')
,('Big Box Store #768')
,('Little Shop #2')
,('Widgets Warehouse #678')
,('Little Shop #5')
,('Widgets Warehouse #559')
,('Corner Boutiques #32*CLOSED')    --notice no space here
,('Corner Boutiques #67 *CLOSED')
,('Corner Boutiques #12')
,('Buy More #1047 SUPERSTORE')
,('1 Stop Shop #3')
,('1 Stop Shop #17')
,('You 2 Me #16');

select
    SUBSTRING(MyString,CHARINDEX('#',MyString,0) + 1,case when PATINDEX('%[^0-9]%',RIGHT(MyString,LEN(MyString) - CHARINDEX('#',MyString,0))) = 0 then 99 else PATINDEX('%[^0-9]%',RIGHT(MyString,LEN(MyString) - CHARINDEX('#',MyString,0))) - 1 end)
    --char version...
   ,SUBSTRING(MyString,CHARINDEX('#',MyString,0) + 1,case when PATINDEX('%[^0-9]%',substring(MyString,CHARINDEX('#',MyString,0) + 1,LEN(MyString) - CHARINDEX('#',MyString,0) + 1)) = 0 then 99 else PATINDEX('%[^0-9]%',substring(MyString,CHARINDEX('#',MyString,0) + 1,LEN(MyString) - CHARINDEX('#',MyString,0) + 1)) - 1 end)

from
    @t

edited Sep 12 '17 at 16:05

answered Sep 12 '17 at 15:12

S3S

24,809
5
26
45

This looks great and I tested and it works on the dataset, but my data type is a char(65). Your solution appears to only work on varchars and nvarchars. Am I doing something wrong or is there an alternate way? I even threw in some "CONVERT" functions to convert them to varchar(65), but that didn't work. Thanks! – jderekc Sep 12 '17 at 15:56
@jderekc i added a `CHAR` version. The `RIGHT()` function only accepts `NVARCHAR` and `VARCHAR`. I replaced this with `SUBSTRING` – S3S Sep 12 '17 at 16:06
This is perfect! Learn something new every day. Thanks so much for your help, @scsimon. – jderekc Sep 12 '17 at 16:10
No worries at all @jderekc – S3S Sep 12 '17 at 16:10

score 0 · Answer 3 · edited Sep 12 '17 at 15:41

please try this, using pathindex

    DECLARE @TABLE AS TABLE(
name_col varchar(250)
)

INSERT INTO @TABLE
SELECT
'Big Box Store #450' UNION ALL
SELECT'Big Box Store #768'UNION ALL
SELECT'Little Shop #2'UNION ALL
SELECT'Widgets Warehouse #678'UNION ALL
SELECT'Little Shop #5'UNION ALL
SELECT'Widgets Warehouse #559'UNION ALL
SELECT'Corner Boutiques #32 *CLOSED'UNION ALL
SELECT'Corner Boutiques #67 *CLOSED'UNION ALL
SELECT'Corner Boutiques #12'UNION ALL
SELECT'Buy More #1047 SUPERSTORE'UNION ALL
SELECT'1 Stop Shop #3 132132'UNION ALL
SELECT'1 Stop Shop #17'UNION ALL
SELECT'You 2 Me #16'

SELECT 
LEFT(SUBSTRING(
     SUBSTRING(name_col,CHARINDEX('#',name_col) + 1,LEN(name_col)), 
     PATINDEX('%[0-9.-]%',SUBSTRING(name_col,CHARINDEX('#',name_col) + 1,LEN(name_col))), 8000),
     PATINDEX('%[^0-9.-]%', SUBSTRING(SUBSTRING(name_col,CHARINDEX('#',name_col) + 1,LEN(name_col)), 
     PATINDEX('%[0-9.-]%', SUBSTRING(name_col,CHARINDEX('#',name_col) + 1,LEN(name_col))), 8000) + 'X') -1)

FROM @TABLE

Your suspicions were right, using PATINDEX is the solution, please check the link for more info.

SQL - Get numbers from string after a pound (#) sign

3 Answers3