Extract String Between Specific Instance of Space - SQL Server

问题: 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 numbe...

问题:

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!


回答1:

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


回答2:

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)

回答3:

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.


回答4:

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
  • 发表于 2019-01-18 19:56
  • 阅读 ( 212 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除