SQL statement to retrieve specific string

Question

I'm trying to extract a number from a Notes column. I only want the number if it is preceded by a specific string of characters. The number can be 5 or 6 characters long, and may eventually go up to 7 or more in the future.

What combination of LEFT, RIGHT, CHARINDEX, or SUBSTRING do I need to utilize to accomplish this? or do I need to use something else altogether? I haven't been able to figure anything out that works.

The Notes column contains lots of different kinds of notes as well, so that's been making it difficult.

Thanks in advance.

EDIT: Sorry, here's some sample data and expected output.

EDIT2: Sorry again, I should've made the sample data a bit more clear. There are multiple numbers, but I only want the numbers that are preceded by 'Account #'.

    Sample Data           Output
    'Account #12345'      12345
    'Account #123456'     123456
    'Random #12345'       NULL
    'Account #1234567'    1234567
    '12345'               NULL
    'Random'              NULL

Possible duplicate of [Query to get only numbers from a string](https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) — ViKiNG, Dec 28 '17 at 22:46
@ViKiNG, Yes this is a related question, but the accepted answer is - uhm - cummulating a lot of *no-go*: scalar UDF together with a `WHILE` loop! Pure procedural thinking... — Shnugo, Dec 28 '17 at 23:15

Horaciux · Answer 1 · 2017-12-29T15:40:17.793

1

This ensures 5 digits after 'Account #' and ask for isnumeric when longer. This is not 100% your request but is an approach. Isnumeric function works in a special way https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql

--len('Account #') is 9

    select case when Notes like 'Account #[0-9][0-9][0-9][0-9][0-9]%' 
          and isnumeric(right(Notes,len(Notes)-9) )=1 then right(Notes,len(Notes)-9) 
         else null end
        from myTable

For SQLServer 2012+ use try_parse https://learn.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql

    select case when Notes like 'Account #[0-9][0-9][0-9][0-9][0-9]%' 
          and isnumeric(try_parse(right(Notes,len(Notes)-9) as bigint ))=1 
    then right(Notes,len(Notes)-9) else null end
     from myTable

SQLFIDDLE http://sqlfiddle.com/#!6/cd617/2

edited Dec 29 '17 at 15:40

answered Dec 28 '17 at 22:46

Horaciux

6,322
2
22
41

This one looks like it may work, but I'm running into an error even after fixing the syntax ones (missing commas and a closing parenthesis). Msg 536, Level 16, State 1, Line 1 Invalid length parameter passed to the right function. – kaiguy Dec 29 '17 at 15:03
1

@kaiguy see my edit, `right` function was wrong also – Horaciux Dec 29 '17 at 15:20
Yes, it runs without any issues now. Unfortunately, it is only returning NULL values. – kaiguy Dec 29 '17 at 15:33
@kaiguy Both queries work as expected see http://sqlfiddle.com/#!6/cd617/2 – Horaciux Dec 29 '17 at 15:39

score 1 · Answer 2 · answered Dec 28 '17 at 23:09

You might try this:

DECLARE @dummyTbl TABLE(YourString VARCHAR(100));
INSERT INTO  @dummyTbl VALUES
                      ('Account #12345')
                     ,('Account #123456')
                     ,('Random # note')
                     ,('Account #1234567');
WITH Casted AS
(
    SELECT YourString
          ,CAST('<x>' + REPLACE(YourString,' #','</x><x>') + '</x>' AS XML) toXML
    FROM @dummyTbl 
)
SELECT YourString
      ,toXML
      ,toXML.value('x[1]','nvarchar(max)') AS KeyText
      ,toXML.value('x[2] cast as xs:int?','int') AS NumberIfCastable 
FROM Casted;

The result

YourString        toXML                         KeyText NumberIfCastable
Account #12345    <x>Account</x><x>12345</x>    Account    12345
Account #123456   <x>Account</x><x>123456</x>   Account    123456
Random # note     <x>Random</x><x> note</x>     Random     NULL
Account #1234567  <x>Account</x><x>1234567</x>  Account    1234567

You can see, that I used a trick with CAST and REPLACE to transform your string to an XML which allows to address each part separately. The XPath to the first element x[1] returns Account or Random while the second x[2] returns the number.

A second trick I use is XQuerys implicit ability to try_cast a value. If the value cannot be converted to an xs:int? it will be returned as a NULL.

Thanks for the response! There's a lot of new tools I will try using in the future. However, I'm having trouble getting this to work with data from a table. instead of the temp dummy table, how would I use data from a Notes column from an Orders table? — kaiguy, Dec 29 '17 at 14:54
@kaiguy If you take the code from `WITH` down to the end and replace `@dummyTbl` with `Orders` and `YourString` with `Note`, this should work... Add `WHERE LEFT(Note, LEN('Account #'))='Account #'` to read the rows with this beginning only... — Shnugo, Dec 29 '17 at 19:27

Yogesh Sharma · Answer 3 · 2017-12-30T04:34:13.743

I would use substring() with patindex() function to get the numeric values only

SELECT sampledata, SUBSTRING(sampledata, PATINDEX('%[1-9]%', SampleData),
       case
            when(LEN(sampledata)-PATINDEX('%[1-9]%', SampleData)+1) > LEN(SampleData)
            then 0
            else LEN(sampledata)-PATINDEX('%[1-9]%', SampleData)+1
      end) numericvalues
FROM table;

EDIT:

Changing the question after you have asked it is rude. It can invalidate answers which in turn can attract down votes.

Well, i updated the answer according to the edited post.

select sampledata,
      case 
          when patindex('%Account #%', sampledata) > 0
          then SUBSTRING(sampledata, PATINDEX('%[1-9]%', sampledata), LEN(sampledata)-PATINDEX('%[1-9]%', sampledata)+1)
      end [Output]
from table

Result :

 Sample Data           Output
'Account #12345'      12345
'Account #123456'     123456
'Random #12345'       NULL
'Account #1234567'    1234567
'12345'               NULL
'Random'              NULL

Thanks for the response, but I don't think this will work in my situation. I've updated my sample data to indicate there are more numbers other than what I included. — kaiguy, Dec 29 '17 at 15:08

score 1 · Accepted Answer · answered Dec 29 '17 at 16:03

This should do it.

SELECT YT.[Sample Data],
       S2.[Output]
FROM   YourTable YT
       CROSS APPLY (SELECT 'Account #') S(S)
       CROSS APPLY (SELECT CASE
                             WHEN CHARINDEX(S.S, [Sample Data]) > 0
                               THEN SUBSTRING([Sample Data], LEN(S.S) + CHARINDEX(S.S, [Sample Data]), 8000)
                           END) S1(StringWithNumberAtStart)
       CROSS APPLY (SELECT LEFT(StringWithNumberAtStart, 
                                PATINDEX('%[^0-9]%', StringWithNumberAtStart + 'X'))) S2([Output])

SQL statement to retrieve specific string

4 Answers4