Extract String Between Specific Instance of Space - SQL Server

Question

I am trying to extract the numbers out of the strings below in SQL Server. I have looked at other questions but am struggling to find a solution that will capture the numbers regardless of space indexes. My challenge is extracting these numbers when the number of spaces until the number is not static.

The numbers are always before the last instance of space and before the K.

Item Name
----------------------
AA BB CCC DDDD E 1.5 K
CC EEE-EEE 0.5 K
CC EEEEEEE 7 K
CC DD EEE-EEE 0.5 K
DDDD EEE 5 K
DDDD EEE EEE 0.5 K

I have tried without luck among other variations:

SELECT LEFT(ITEMNAME, CHARINDEX(' ', ITEMNAME)), 
   LEFT(ITEMNAME, PATINDEX('%[^0-9]%', ITEMNAME)) as [Item Name]

My desired output would be:

Item Name
---------
1.5
0.5
7
0.5
5
0.5

Any ideas on how to extract this? Thanks!

are your numbers always the next to last element? And is the last element always K? — S3S
– S3S, Commented Jan 14, 2019 at 18:54
Yes, sorry I did not specify. It will always before the last space and the last element is always K. — choloboy
– choloboy, Commented Jan 14, 2019 at 18:55
Does your string always ends with ' K'? (white space + K) — Ilyes
– Ilyes, Commented Jan 14, 2019 at 19:35

Ilyes · Accepted Answer · 2019-01-14 19:55:48Z

2

You can use CROSS APPLY as

SELECT *
FROM T CROSS APPLY
     (
       SELECT value
       FROM STRING_SPLIT(ItemName, ' ')
     ) TT
WHERE TRY_CAST(Value AS DECIMAL) IS NOT NULL;

Live Demo

Since you confirm that your string is always ends with ' K' and the number is before it, simply use

SELECT *, 
       SUBSTRING(ItemName, PATINDEX('%[0-9]%', itemname), 
                 LEN(ItemName) -
                 (
                 PATINDEX('%[0-9]%', itemname)+
                 CHARINDEX('K', REVERSE(ItemName))
                 )
                 )Result
FROM T;

OR

SELECT *, 
       RIGHT(REPLACE(ItemName, ' K', ''),
             CHARINDEX(' ', REVERSE(REPLACE(ItemName, ' K', '')))-1
            ) Result
FROM T;

Live Demo

edited Jan 14, 2019 at 19:55

answered Jan 14, 2019 at 19:01

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S3S · Accepted Answer · 2019-01-14 18:57:36Z

1

Since it will always be the next to last element and the last element is K you can simplify it by removing K and taking everything up to the first space.

select 
    right(rtrim(replace(item_name,'K','')),charindex(' ',reverse(rtrim(replace(item_name,'K','')))))
from 
    (values ('AA BB CCC DDDD E 1.5 K'), ('CC EEE-EEE 0.5 K'), ('CC EEEEEEE 7 K')) v(item_name)

answered Jan 14, 2019 at 18:57

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-01-14 18:54:49Z

0

This is a pain in SQL Server, because the string manipulation functions are rather primitive. But it can be done:

with t as (
      select v.*
      from (values ('AA BB CCC DDDD E 1.5 K'), ('CC EEE-EEE 0.5 K'), ('CC EEEEEEE 7 K')) v(item_name)
     )
select t.*, v2.s2
from t cross apply
     (values (stuff(t.item_name, 1, patindex('%[0-9]%', t.item_name) - 1, ''))
     ) v1(s1) cross apply
     (values (left(v1.s1, charindex(' ', v1.s1)))) v2(s2);

Here is a db<>fiddle.

answered Jan 14, 2019 at 18:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Himanshu · Accepted Answer · 2019-01-14 19:13:03Z

0

I guess need to play around with pat index to get first occurence and last occurence

  SELECT 
  CHARINDEX(PATINDEX('%[^0-9]%', ITEMNAME))
  +
     CHARINDEX(PATINDEX('%[0-9^]%', ITEMNAME))


  FROM TABLE

answered Jan 14, 2019 at 19:13

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Collectives™ on Stack Overflow

Extract String Between Specific Instance of Space - SQL Server

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related