sql取出数字

[好帅的一条鱼]

‧白有2000加2.5
‧白轉晚有加5.25(假)
‧晚有0800加3

如果有上面一组数据，我都要取出“加”后面的那个数值，最后得到的结果为2.5，5.25，3。请问怎么做呢？

[水族杰纶]

declare @t table(col nvarchar(30))
insert @t select N'‧白有2000加2.5' 
insert @t select N'‧白轉晚有加5.25(假)' 
insert @t select N'‧晚有0800加3' 
SELECT REPLACE(RIGHT(COL,LEN(COL)-CHARINDEX(N'加',COL)),N'(假)','') FROM @T
/*                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2.5
5.25
3

*/

[SQL77]

SELECT SUBSTRING(COL1,CHARINDEX('加',COL1+'(')+1,CHARINDEX('(',COL1+'(')-1) FROM TB

[csdyyr]

DECLARE @TB TABLE([COL] NVARCHAR(12))
INSERT @TB 
SELECT N'白有2000加2.5' UNION ALL 
SELECT N'白轉晚有加5.25(假)' UNION ALL 
SELECT N'晚有0800加3'

SELECT LEFT(COL,PATINDEX('%[^0-9^.]%', COL+'X')-1)
FROM (
SELECT COL=STUFF(COL,1,CHARINDEX(N'加',COL),'')
FROM @TB ) T
/*
2.5
5.25
3
*/

[wzy_love_sly]

这需要规律，每个要取的数字前面都是“加”？后面可以跟些什么？

[ks_reny]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL codedeclare @t table(col nvarchar(30))
insert @t select N'‧白有2000加2.5'
insert @t select N'‧白轉晚有加5.25(假)'
insert @t select N'‧晚有0800加3'
SELECT REPLACE(RIGHT(COL,LEN(COL)-CHARINDEX(N'加',COL)),N'(假)','') FROM @T
/* …
[/Quote]
要是數字後面還有其他漢字呢?怎麼處理?

[chuifengde]

DECLARE @a TABLE(a NVARCHAR(200))
INSERT @a SELECT '‧白有2000加2.5' 
union all select '‧白轉晚有加5.25(假)' 
union all select '‧晚有0800加3' 
SELECT substring(a+',',charindex('加',a)+1,PATINDEX('%[^0-9.]%',stuff(a+',',1,CHARINDEX('加',a),''))-1)
FROM @a

--result
/*                                                   
-----------------
2.5
5.25
3
（所影响的行数为 3 行）
*/

[林虎]

...

[叶子]

create function [dbo].[get_number2](@s varchar(100))
returns varchar(100)
as
begin
while patindex('%[^0-9.]%',@s) > 0
begin
set @s=stuff(@s,patindex('%[^0-9.]%',@s),1,'')
end
return @s
end

go

declare @table table (col varchar(19))
insert into @table
select '.白有2000加2.5' union all
select '.白轉晚有加5.25(假)' union all
select '.晚有0800加3'

select dbo.get_number2(substring(col,charindex('加',col),len(col)-charindex('加',col)+1)) as col from @table

/*
col
------
2.5
5.25
3
*/

[SQL77]

declare @t table(col nvarchar(30))
insert @t select '白有2000加2.5' 
insert @t select '白轉晚有加5.25(假)' 
insert @t select '晚有0800加3' 

SELECT SUBSTRING(COL,CHARINDEX(N'加',COL+'(')+1,CHARINDEX(N'(',COL+'(')-CHARINDEX(N'加',COL+'(')-1) FROM @t

[好帅的一条鱼]

取出的这个值如何被转换为int。cast和convert都报错呢，“在将 nvarchar 值 '3.5' 转换成数据类型 int 时失败。”

[ks_reny]

[Quote=引用 8 楼 maco_wang 的回复:]
SQL code
create function [dbo].[get_number2](@s varchar(100))
returns varchar(100)
as
begin
while patindex('%[^0-9.]%',@s) > 0
begin
set @s=stuff(@s,patindex('%[^0-9.]%',@s),1,'')
end
return @s
end

go

declare @table table (col varchar(19))
insert into @table
select '.白有2000加2.5' union all
select '.白轉晚有加5.25(假)' union all
select '.晚有0800加3'

select dbo.get_number…
[/Quote]
高手呀。这也许是楼主的要求吧。

[好帅的一条鱼]

取出的这个值如何被转换为int。cast和convert都报错呢，“在将 nvarchar 值 '3.5' 转换成数据类型 int 时失败。”
在给帮帮

[sharelove]

搞這麼麻煩都是你設計的時候搞出來的問題！一般的不規則取值就算啦，你還搞出這麼多不規則的來！

[无心雨云]

学习