我有一列数据，其中包含要转换为数字的百分比范围的字符串，因此我可以轻松进行比较。

字符串中可能的值：

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

我想将其在我的select where子句中转换为仅第一个数字5、10、15等，以便可以将该值与传入的"至少这个"值进行比较。

我已经尝试了一些关于子字符串，charindex，convert和replace的变体，但是我仍然无法获得在所有组合中都可以使用的东西。

有任何想法吗？

解决方案

回答

我们可以将char数据转换为其他类型的char(将char(10)转换为varchar(10))，但是无法从SQL内将字符数据转换为整数数据。

回答

我不知道这在SQL Server中是否有效，但是在MySQL中，我们可以使用多种技巧将字符数据转换为数字。样本数据中的示例：

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

现在显然这使第一次测试失败，但是在字符串开头进行一些巧妙的字符串替换就足以使其正常工作。

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

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

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

回答

我们最好将" <5％"和" 5-10％"更改为在2个字段中存储2个值。而不是存储" <5％"，我们将存储0和5，而不是" 5-10％"，我们将最终得到5和10. 我们将最终得到2列，一列称为lowerbound，一列称为上限，然后仅检查值> =下限AND值<上限。

回答

试试这个，

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

经过我的测试，它可以正常工作，这只是我的第一次尝试，因此我们可能可以对其进行优化。

回答

我们可以在带有游标的sql server中执行此操作。如果我们可以创建CLR函数来提取编号分组，那将会有所帮助。它在T-SQL中可能出现，只是很难看。

创建光标以遍历列表。
查找第一个数字，如果他们中只有一个数字组，则将其返回。否则，找到第二项分组。

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

只需将结果值设置回表

回答

我们遇到的问题是没有保持原子数据的症状。在这种情况下，它看起来纯粹是无意识的(传统)，但这是一个链接。

要从中设计自己，请创建一个range_lookup表：

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.

回答

@马丁：解决方案有效。

这是我根据@mercutio的灵感想到的另一个

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

回答

这很复杂，但是对于我们提供的测试用例，这是可行的。只需将@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