sql取出数字

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

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

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*/
SELECT SUBSTRING(COL1,CHARINDEX('加',COL1+'(')+1,CHARINDEX('(',COL1+'(')-1) FROM TB
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
*/
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
endgodeclare @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
*/
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 时失败。”
取出的这个值如何被转换为int。cast和convert都报错呢，“在将 nvarchar 值 '3.5' 转换成数据类型 int 时失败。”
在给帮帮