本文介绍了查询以提取数字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE TABLE #TEMP(ID INT,Descr VARCHAR(300)) INSERT INTO #TEMP SELECT 1,'名称ZZZ990 - ZZZ'UNION ALL的990% - 没有空格黑白ZZZ和990 选择2,'名称ZZZ 660 - 670%ZZZ'UNION ALL - ZZZ和660之间的空间选择3,'名称12%ZZZ OF ZZZ' - ZZZ在% 之后SELECT * FROM #TEMP
需要提取我案例中的百分比数字990,660& 12进入单独的字段。
我们有3种不同格式的数据用于百分比。
解决方案CREATE TABLE #TEMP(ID INT,Descr VARCHAR(300)) INSERT INTO #TEMP SELECT 1,'NAME ZZZ990 - 990%of ZZZ'UNION ALL - 没有空间b / w ZZZ和990 SELECT 2,'NAME ZZZ 660 - 660%ZZZ'UNION ALL - ZZZ和660之间的空间 SELECT 3,'NAME 12%ZZZ OF ZZZ ' - ZZZ在% SELECT *, RTRIM(反向(子串(东西)(反向(描述),1,patindex('%[%]%') ,reverse(Descr)),''),1,charindex('',Stuff(Reverse(Descr),1,patindex('%[%]%',reverse(Descr)),'')))))) FROM #TEMP drop table #TEMP
CREATE TABLE #TEMP (ID INT, Descr VARCHAR(300)) INSERT INTO #TEMP SELECT 1, 'NAME ZZZ990 - 990% OF ZZZ' UNION ALL -- No space b/w ZZZ and 990 SELECT 2, 'NAME ZZZ 660 - 660% OF ZZZ' UNION ALL -- Space between ZZZ and 660 SELECT 3, 'NAME 12% ZZZ OF ZZZ' -- ZZZ is after % SELECT * FROM #TEMPNeed to extract the percentage number in my case 990,660 & 12 into separate field.
We have 3 different formats of data for percentage.
解决方案 CREATE TABLE #TEMP (ID INT, Descr VARCHAR(300)) INSERT INTO #TEMP SELECT 1, 'NAME ZZZ990 - 990% OF ZZZ' UNION ALL -- No space b/w ZZZ and 990 SELECT 2, 'NAME ZZZ 660 - 660% OF ZZZ' UNION ALL -- Space between ZZZ and 660 SELECT 3, 'NAME 12% ZZZ OF ZZZ' -- ZZZ is after % SELECT * , RTRIM(Reverse(Substring(Stuff(Reverse(Descr),1,patindex('%[%]%',reverse(Descr)),''),1,charindex(' ',Stuff(Reverse(Descr),1,patindex('%[%]%',reverse(Descr)),''))) )) FROM #TEMP drop table #TEMP
更多推荐
查询以提取数字
发布评论