代码之家 › 专栏 › 技术社区 › Geoff

从SQL字符串范围中获取数字

sql-server

Geoff · 技术社区 · 16 年前

我有一列数据,其中包含一个百分比范围,作为一个字符串,我想将其转换为一个数字,这样我就可以进行简单的比较。

字符串中的可能值:

'<5%'
'5-10%'
'10-15%'
...
'95-100%'

我想在select where子句中将这个值转换为第一个数字、5、10、15等,这样我就可以将这个值与传入的“至少这个”值进行比较。

我试过在substring、charindex、convert和replace上使用许多变体,但是我仍然无法获得在所有组合中都有效的东西。

有什么想法吗?

8 回复 | 直到 16 年前

Martin 16 年前

试试这个,

SELECT substring(replace(interest , '<',''), patindex('%[0-9]%',replace(interest , '<','')), patindex('%[^0-9]%',replace(interest, '<',''))-1) FROM table1

在我的测试结束,它工作,这只是我的第一次尝试,所以你可以优化它。

Geoff 16 年前

@马丁:你的解决办法行得通。

这是我根据@mercutio的灵感设计的另一个

select cast(replace(replace(replace(interest,'<',''),'%',''),'-','.0') as numeric) test
from table1 where interest is not null

Shazburg 16 年前

您可以将char数据转换为其他类型的char(将char(10)转换为varchar(10)),但不能从SQL中将字符数据转换为整数数据。

mercutio 16 年前

我不知道这在SQL Server中是否有效,但在MySQL中,您可以使用几个技巧将字符数据转换为数字。示例数据中的示例:

"<5%"     => 0
"5-10%"   => 5
"95-100%" => 95

显然,这没有通过您的第一个测试,但是在字符串的开头进行一些聪明的字符串替换就足够让它工作了。

将字符数据转换为数字的一个示例:

SELECT "5-10%" + 0 AS foo ...

可能在SQL Server中不起作用,但将来的搜索可能会帮助奇怪的MySQL用户:-d

AdamSane 16 年前

您可以在SQL Server中使用光标执行此操作。如果您可以创建一个clr函数来提取有帮助的数字分组。在T-SQL中是可能的,只是会很难看。

创建光标以在列表上循环。找到第一个数字,如果它们中只有一个数字组,则返回它。否则,请查找第二个项目分组。

如果只返回第一个项目分组,并且列表中的第一个项目将其设置为上限。如果只返回了第一个项目分组,并且列表中的最后一个项目将其设置为下限。否则,将第一个项分组设置为下限,将第二个项分组设置为上限。

只需将结果值设置回表

jason saldo 16 年前

您遇到的问题是不保持数据原子性的症状。在这种情况下,它看起来完全是无意的(遗留的),但这里是 link 关于它。

要设计出这样的样式,请创建一个范围查找表:

Create table rangeLookup(
    rangeID int  -- or rangeCD or not at all
    ,rangeLabel varchar(50)
    ,LowValue int--real or whatever
    ,HighValue int 
)

要想在这里找到一些伪步骤,这将是一个非常复杂的混乱。

normalize your input by replacing all your crazy charecters.
    replace(replace(rangeLabel,"%",""),"<","")
    --This will entail many nested replace statments.

Add a CASE and CHARINDEX to look for a space if there is none you have your number
    else use your substring to take everything before the first " ".
    -- theses steps are wrapped around the previous step.

Kibbee 16 年前

这很复杂,但是对于您提供的测试用例,这是可行的。只需将@test替换为您从表中查找的列。

DECLARE @TEST varchar(10)

set @Test =  '<5%'
--set @Test =  '5-10%'
--set @Test =  '10-15%'
--set @Test =  '95-100%'

Select CASE WHEN 
Substring(@TEST,1,1) = '<' 
THEN 
0
ELSE 
CONVERT(integer,SUBSTRING(@TEST,1,CHARINDEX('-',@TEST)-1))
END
AS LowerBound
,
CASE WHEN 
Substring(@TEST,1,1) = '<'
THEN
CONVERT(integer,Substring(@TEST,2,CHARINDEX('%',@TEST)-2))
ELSE
CONVERT(integer,Substring(@TEST,CHARINDEX('-',@TEST)+1,CHARINDEX('%',@TEST)-CHARINDEX('-',@TEST)-1))
END
AS UpperBound

Rajesh 10 年前

换衣服你可能会好多了 <5% 和 5-10% 在2个字段中存储2个值。而不是存储 <5% ,您将存储0和5,而不是 5-10% 最后你会得到5分和10分。最后是两列,一列叫LowerBound,一列叫UpperBound,然后只检查值 >= 下限和值 < 上界。