table1中有name的字段,字段的数据如下:
name
CHINA1-RUN2K-RUN2K_ALL-TYCOON-211
CHINA2-HIS2K-HIS2K_ALL-MODERK-2316请问SQL该如何写才能把上面name中的"TYCOON"和"MODERK"找出来?
name
CHINA1-RUN2K-RUN2K_ALL-TYCOON-211
CHINA2-HIS2K-HIS2K_ALL-MODERK-2316请问SQL该如何写才能把上面name中的"TYCOON"和"MODERK"找出来?
insert into tb values('CHINA1-RUN2K-RUN2K_ALL-TYCOON-211')
insert into tb values('CHINA2-HIS2K-HIS2K_ALL-MODERK-2316')
goselect name , new_name = parsename(replace(reverse(substring(reverse(name),charindex('-',reverse(name)) + 1,len(name))),'-','.'),1) from tbdrop table tb/*
name new_name
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
CHINA1-RUN2K-RUN2K_ALL-TYCOON-211 TYCOON
CHINA2-HIS2K-HIS2K_ALL-MODERK-2316 MODERK(所影响的行数为 2 行)
*/
--功能:取第@i个分割符前的字符串
--Author:josy(百年树人)
--参数@s:字符串
--参数@i:分割符的位置
--参数@sign:分隔符
**/
create function [dbo].[f_col](@s varchar(100),@i int,@sign varchar(10))
returns varchar(20)
as
begin
declare @t table(id int identity(1,1),col varchar(10));
declare @cnt int,@rel varchar(20)
set @s=@s+@sign
set @cnt=len(@s)-len(replace(@s,@sign,''))
while @cnt>0
begin
insert @t(col) select left(@s,charindex(@sign,@s)-1)
set @cnt=@cnt-1
set @s=stuff(@s,1,charindex(@sign,@s),'')
end
select @rel=col from @t where id=@i
return @rel
end
go
select dbo.f_col(name,4,'-') as name from tb/**
name
--------------------
TYCOON
MODERK(2 行受影响)
**/