SPEC NO.:123456
SPEC NO.:789012
SPEC NO.:33668535
SPEC NO.:2587981在数据库里的一个字段如上。我想截取
SPEC NO.:后面的字符,只截五位,请问sql怎么写?注意SPEC后面有一个空格。
SPEC NO.:789012
SPEC NO.:33668535
SPEC NO.:2587981在数据库里的一个字段如上。我想截取
SPEC NO.:后面的字符,只截五位,请问sql怎么写?注意SPEC后面有一个空格。
调试欢乐多
SPEC NO.:789012 ->78901
SPEC NO.:33668535 ->33668
SPEC NO.:2587981 ->25879
就像这样。
不行,SPEN前面还有一大堆乱的数据比如abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs
fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda
或者left([SPEC NO],5)
abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs
fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda
12fastaqf21d3a fad SPEC NO.:3344580 asf21354 如果是这样呢?
select 'abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs ' union all
select 'fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda'
select substring(a,charindex('NO.:',a)+4,5) from @table
33668(所影响的行数为 1 行)
内容大概是下面这样abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs
fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda
12fastaqf21d3a fad SPEC NO.:3344580 asf21354 提取 SPEC NO.:后面的五位。。
SPEC NO.:123456 提取后是12345
SPEC NO.:789012 提取后是78901
SPEC NO.:33668535 提取后是33668
SPEC NO.:2587981 ->25879
declare @table table(a varchar(500))insert into @table
select 'abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs ' union all
select 'fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda' union all
select '12fastaqf21d3a fad SPEC NO.:3344580 asf21354 'select
[结果]=substring(substring(a,charindex(':',a),len(a)-charindex(':',a)+1),2,charindex(' ',substring(a,charindex(':',a),len(a)-charindex(':',a)+1))-2)
from @table
/*
值
123456
67890
3344580
*/
select substring(time,charindex('NO.:',time)+4,5) from test
楼上正解
select SUBSTRING([time],CHARINDEX('NO.:',[time])+4,5) from test
内容大概是下面这样 abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs
fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda
12fastaqf21d3a fad SPEC NO.:3344580 asf21354 提取 SPEC NO.:后面的五位。。 fadfSPEC NO.:123456 提取后是12345
abgSPEC NO.:789012 提取后是78901
teadSPEC NO.:33668535 提取后是33668
12487SPEC NO.:2587981 提取后是25879
下面还有上千条像上面这样的。。
from[test]
set @str='fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda'
select substring(@str,charindex(':',@str)+1,5)
/*
67890
*/
insert @t select 'abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs'
insert @t select 'fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda'
insert @t select '12fastaqf21d3a fad SPEC NO.:3344580 asf21354'select *,substring(col,charindex('NO.:',col)+4,5) as result
from @t
/*(1 行受影响)(1 行受影响)(1 行受影响)
col result
------------------------------------------------------------ ----------
abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs 12345
fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda 67890
12fastaqf21d3a fad SPEC NO.:3344580 asf21354 33445(3 行受影响)*/
declare @testtable(time varchar(60))
insert @test select 'abcfadfd dafsb asdf ds SPEC NO.:123456 fdsaf fdasfs'
insert @test select 'fdadafsb fdf ds SPEC NO.:67890 fdsaf fdafda'
insert @test select '12fastaqf21d3a fad SPEC NO.:3344580 asf21354'
select *,substring(time ,charindex('NO.:',time)+4,5) as result
from @test
我测试过了,是可以的