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

CodePudding user response：

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:

CodePudding user response：

1. reverse() the string
2. use patindex() to find non numeric character
3. use right() to extract the characters

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