求一条在字符串中取出数字的SQL解决办法

求一条在字符串中取出数字的SQL
表A如下：
ID 范围
1 -10<结果<10
2 10>结果>-10
3 -10<结果
4 结果<10
5 结果>-10
6 10>结果

里面有且只有上面6种可能，我想是如果只有一个数字的，就直接取出，两个数字就取两个数字的平均值，结果应该这样：

ID 结果
1 0
2 0
3 -10
4 10
5 -10
6 10

求指点

------解决方案--------------------

SQL code

create table #tb(id int,范围 varchar(100))
insert #tb
select 
1, '-10<结果<10'
union select
2 ,'10>结果>-10'
union select
3 ,'-10<结果'
union select
4 ,'结果<10'
union select
5,'结果>-10'
union select
6, '10>结果' 
select id, isnull(cast(substring(范围,1,CHARindex('结',范围)-2) as int),0)+isnull(cast(substring(范围,CHARindex('果',范围)+2,LEN(范围)-CHARindex('果',范围)) as int),0)
from 
(select id, case when left(范围,1)='结' or right(范围,1)='果' then ' '+范围+' ' else 范围 end 范围 from #tb) a

------解决方案--------------------

SQL code

if object_id('[TBA]') is not null drop table [TBA]
go
create table [TBA] (ID int,范围 nvarchar(18))
insert into [TBA]
select 1,'-10<结果<10' union all
select 2,'10>结果>-10' union all
select 3,'-10<结果' union all
select 4,'结果<10' union all
select 5,'结果>-10' union all
select 6,'10>结果'

select * from [TBA]


WITH TT
AS(
SELECT id,REPLACE(REPLACE(REPLACE(范围,'结果','.'),'>','.'),'<','.') AS col
FROM TBA),

T1
AS(
SELECT id, CASE WHEN LEFT(col,1) = '.' THEN '0.' ELSE '' END +col+CASE WHEN RIGHT(col,1) = '.' THEN '.0' ELSE '' END AS col
FROM TT)

SELECT id,
CONVERT(INT,ISNULL(PARSENAME(col,1),0))
+CONVERT(INT,ISNULL(PARSENAME(col,2),0))
+CONVERT(INT,ISNULL(PARSENAME(col,3),0))
+CONVERT(INT,ISNULL(PARSENAME(col,4),0)) AS NO
FROM T1 

/*
id          NO
----------- -----------
1           0
2           0
3           -10
4           10
5           -10
6           10

(6 行受影响)
*/

------解决方案--------------------

SQL code


IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')
BEGIN
    DROP TABLE A
END
GO
CREATE TABLE A
(
    ID INT,
    RG VARCHAR(100)
)
GO
INSERT INTO A
SELECT 1, '-10<结果<10' UNION
SELECT 2, '10>结果>-10' UNION
SELECT 3, '-10<结果' UNION
SELECT 4, '结果<10' UNION
SELECT 5, '结果>-10' UNION
SELECT 6, '10>结果'
GO
WITH t0 AS
(
    SELECT ID,REPLACE(RG,'结果','') AS RG
    FROM A
)

SELECT ID,CASE WHEN LEN(RG) - LEN(REPLACE(RG,'<','')) = 2 THEN CAST(LEFT(RG,CHARINDEX('<',RG) - 1) AS INT) + CAST(RIGHT(RG,LEN(RG) - CHARINDEX('<',RG) - 1) AS INT)
               WHEN LEN(RG) - LEN(REPLACE(RG,'<','')) = 1 THEN CAST(REPLACE(RG,'<','') AS INT)
               WHEN LEN(RG) - LEN(REPLACE(RG,'>','')) = 2 THEN CAST(LEFT(RG,CHARINDEX('>',RG) - 1) AS INT) + CAST(RIGHT(RG,LEN(RG) - CHARINDEX('>',RG) - 1) AS INT)
               WHEN LEN(RG) - LEN(REPLACE(RG,'>','')) = 1 THEN CAST(REPLACE(RG,'>','') AS INT) END
FROM t0

ID    (No column name)
1    0
2    0
3    -10
4    10
5    -10
6    10

求一条在字符串中取出数字的SQL解决办法

相关推荐