问题描述
我有一张桌子,它有 3 列.第一列
是包含值(数字)和单位(百分比等)的数据,第二列是数字列
,第三个是Unit column
.我想要做的是从第一列中拆分数字和单位,然后将这些拆分后的数据放到其指定的列中.
I have a table and it has a 3 columns. The first column
is the data that contains value(numeric) and unit(percentage and etc..), the second column is numeric column
, the third is Unit column
. What I want to do is split the numeric and the unit from the first column then put those split-ted data to its designated column.
这是我的桌子:
我试过这个功能:SO链接在这里...,它确实可以拆分字母和数字,但是我刚开始使用 SQL 函数,我的问题是参数必须在字符串 STRING
中,所以我所做的是将其更改为子查询但它给了我错误.
I tried this function:SO link here..., it really does splitting alpha and numeric but then I'm new in using SQL Function, my problem there is the parameter must be in string STRING
, so what I did is change it to Sub Query but it gives me error.
示例代码:
SQL 函数:
create function [dbo].[GetNumbersFromText](@String varchar(2000))
returns table as return
(
with C as
(
select cast(substring(S.Value, S1.Pos, S2.L) as int) as Number,
stuff(s.Value, 1, S1.Pos + S2.L, '') as Value
from (select @String+' ') as S(Value)
cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
union all
select cast(substring(S.Value, S1.Pos, S2.L) as int),
stuff(S.Value, 1, S1.Pos + S2.L, '')
from C as S
cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
where patindex('%[0-9]%', S.Value) > 0
)
select Number
from C
)
SELECT STATEMENT 和 SUB 查询:
declare @S varchar(max)
select number from GetNumbersFromText(Select SomeColm From Table_Name) option (maxrecursion 0)
顺便说一句,我使用的是 sql server 2005.
BTW, im using sql server 2005.
谢谢!
推荐答案
如果数字部分总是在开头,那么你可以这样使用:
If the numeric part is always at the beginning, then you can use this:
PATINDEX('%[0-9][^0-9]%', ConcUnit)
获取最后一位的索引.
因此:
DECLARE @str VARCHAR(MAX) = '4000 ug/ML'
SELECT LEFT(@str, PATINDEX('%[0-9][^0-9]%', @str )) AS Number,
LTRIM(RIGHT(@str, LEN(@str) - PATINDEX('%[0-9][^0-9]%', @str ))) As Unit
给你:
Number Unit
-------------
4000 ug/ML
如果数字数据也包含双精度值,那么您可以使用这个:
If numeric data include double values as well, then you can use this:
SELECT LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))
获取最后一位的索引.
因此:
SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str)))
给你数字部分.
还有这个:
SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))) AS Numeric,
CASE
WHEN CHARINDEX ('%', @str) <> 0 THEN LTRIM(RIGHT(@str, LEN(@str) - CHARINDEX ('%', @str)))
ELSE LTRIM(RIGHT(@str, PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))))
END AS Unit
同时提供数字和单位部分.
gives you both numberic and unit part.
以下是我对您发布的数据进行的一些测试:
Here are some tests that I made with the data you have posted:
输入:
DECLARE @str VARCHAR(MAX) = '50 000ug/ML'
输出:
Numeric Unit
------------
50 000 ug/ML
输入:
DECLARE @str VARCHAR(MAX) = '99.5%'
输出:
Numeric Unit
------------
99.5
输入:
DECLARE @str VARCHAR(MAX) = '4000 . 35 % ug/ML'
输出:
Numeric Unit
------------------
4000 . 35 ug/ML
这篇关于使用 sql 拆分字母和数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!