Getting last group of number from string

Question

I have this group of strings:

*INBOX_COL
14AF08
14AF09
17NAMX14
17NAMX16
21KMTZ7
21KMTZ8
21KMTZ9
B-09
B-14

Let's say that the last group of number (7, 8, 9, 14 etc) represents the size.

I want to get the last group of numbers to determine the quantity

SELECT CASE WHEN (RIGHT(RTRIM(INBOX_COL),2) > '16' THEN 6 ELSE 12 END QTY
FROM MY_TABLE

but since the writing is not uniform, so when I took the last two character of string it returns something like this = ('08', '8', '09','9', 'Z7', 'Z8') so it failed the Select Case conditions.

I want to extract the string like these:

14AF08 = 8
17NAMX14 = 14
21KMTZ7 = 7

Any ideas? Thanks

You could use the [last index](https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server/39002164) of a non-digit char. Otherwise just implement your own scalar function to loop from the end and extract the digits. — plalx, Nov 30 '21 at 01:28

score 3 · Answer 1 · answered Nov 30 '21 at 01:35

Not the most generic of methods, but if you knew that the largest number you are looking for is 999 then you only have to check the last 3 chars and then sub-string them out.

select X.INBOX_COL
  -- Substring out the number component
  -- Cheeky way to ignore dash ('-')
  , replace(substring(X.INBOX_COL, Z.StringLength-Z.NumberLength+1, Z.NumberLength),'-','') Quantity
from (
  values
  ('14AF08'),
  ('14AF09'),
  ('17NAMX14'),
  ('17NAMX16'),
  ('21KMTZ7'),
  ('21KMTZ8'),
  ('21KMTZ9'),
  ('B-09'),
  ('B-14')
) X (INBOX_COL)
cross apply (
  -- Assume max value number is 999, so check last 3 characters of string
  values (
    isnumeric(case when len(X.INBOX_COL) > 0 then substring(reverse(X.INBOX_COL),1,1) else 'z' end)
    , isnumeric(case when len(X.INBOX_COL) > 1 then substring(reverse(X.INBOX_COL),2,1) else 'z' end)
    , isnumeric(case when len(X.INBOX_COL) > 2 then substring(reverse(X.INBOX_COL),3,1) else 'z' end)
  )
) Y (One, Two, Three)
cross apply (
  -- Get the expected length of the number string (and the length of the actual string)
  values (
    len(X.INBOX_COL), case when One = 1 and Two = 1 and Three = 1 then 3 when One = 1 and Two = 1 then 2 when One = 1 then 1 else 0 end
  )
) Z (StringLength, NumberLength);

Returns:

INBOX_COL	Quantity
14AF08	08
14AF09	09
17NAMX14	14
17NAMX16	16
21KMTZ7	7
21KMTZ8	8
21KMTZ9	9
B-09	09
B-14	14

wow thanks for the input, I created a function based on your solution now I have another options along with @Squirrel answer to extract the data. — Syns, Nov 30 '21 at 05:20

score 3 · Accepted Answer · answered Nov 30 '21 at 02:45

3

reverse() the string
use patindex() to find non numeric character
use right() to extract the characters

SELECT RIGHT(INBOX_COL, PATINDEX('%[^0-9]%', REVERSE(INBOX_COL)) - 1)

answered Nov 30 '21 at 02:45

Squirrel

23,507
4
34
32

Niiiice.. simple yet met the expectation. thank you – Syns Nov 30 '21 at 05:20

Getting last group of number from string

2 Answers2