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