库里有一个字段列名:TypeName数据是:17_BUS_WOA211
343_BUS_WOA211
如果用substring() 怎么写能得到WOA211 ?第二个"_"以前的串不定长
试了下(if TypeName like'%bus%' then substring(TypeName from ?? for ??) )AS TypeNameONE是这样写么, from ?? for ?? 这个长度怎么得到啊
343_BUS_WOA211
如果用substring() 怎么写能得到WOA211 ?第二个"_"以前的串不定长
试了下(if TypeName like'%bus%' then substring(TypeName from ?? for ??) )AS TypeNameONE是这样写么, from ?? for ?? 这个长度怎么得到啊
select REVERSE(substring(REVERSE(TypeName),1,charindex('_',REVERSE(TypeName))-1)) from tb
INSERT @t SELECT '17_BUS_WOA211'
UNION ALL SELECT '343_BUS_WOA211'SELECT REVERSE(STUFF(REVERSE(v),CHARINDEX('_',REVERSE(v)),1000,'')) FROM @t
CREATE TABLE TEST (TypeName VARCHAR(MAX))INSERT INTO TEST SELECT '17_BUS_WOA211 ' UNION ALL
SELECT '343_BUS_WOA211'
select substring(TypeName,
charindex('_',stuff(TypeName,charindex('_',TypeName,1),1,','),1)+1,len(TypeName)) as TypeName from testdrop table test
INSERT @t SELECT '17_BUS_WOA211'
UNION ALL SELECT '343_BUS_WOA211'SELECT PARSENAME(REPLACE(v,'_','.'),1) FROM @t
declare @s varchar(30),@a varchar(30)
set @s='343_BUS_WOA211 'select right(rtrim(@s),rtrim(len(@s))-PATINDEX('%_W%',rtrim(@s)))/*
------------------------------
WOA211(所影响的行数为 1 行)
*/
go
--创建一个通用的函数
create function [dbo].[split_str]
(
@s varchar(8000), --包含多个数据项的字符串
@index int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)
returns varchar(100)
as
begin
if @s is null return(null)
begin
declare @splitlen int
select @splitlen=len(@split+'A')-2
end
while @index>1 and charindex(@split,@s+@split)>0
begin
select @index=@index-1,@s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
end
return(isnull(left(@s,charindex(@split,@s+@split)-1),''))
endgo
declare @table table (TypeName varchar(140))
insert into @table
select '17_BUS_WOA211' union all
select '343_BUS_WOA211'select dbo.split_str(TypeName,3,'_') as TypeName from @table/*
TypeName
-------------
WOA211
WOA211
*/
--创建函数后可以随意截取go
declare @table table (TypeName varchar(14))
insert into @table
select '17_BUS_WOA211' union all
select '343_BUS_WOA211'select
dbo.split_str(TypeName,1,'_') as col1,
dbo.split_str(TypeName,2,'_') as col2,
dbo.split_str(TypeName,3,'_') as col3
from @table/*
col1 col2 col3
------------------------------
17 BUS WOA211
343 BUS WOA211(2 row(s) affected)
*/
INSERT @t SELECT '17_BUS_WOA211'
UNION ALL SELECT '343_BUS_WOA211'SELECT PARSENAME(REPLACE(v,'_BUS_','.'),1) FROM @t