方法很多了,随便给你一个: declare @s varchar(20)set @s = 'a-Gb-Kc-d-Ie'select top 30 id = identity(int,1,1) into # from sys.objects --2000的话,替换为sysobjectsselect max(charindex('-',col,id)) as pos from( select @s as col )a cross join #drop table #/** 10 **/
DECLARE @s VARCHAR(100) SET @s='a-Gb-Kc-d-Ie' DECLARE @i INT DECLARE @span VARCHAR(1) SET @span = '-' SET @i=4 SELECT '第' + RTRIM(@i) + '个''' + @span + '''在''' + @s + '''中出现的位置是' + RTRIM(LEN(CAST(v AS VARCHAR(100)))+1) FROM (SELECT x=CAST('<r>' + REPLACE(@s,@span,'</r><r>') + '</r>' AS XML)) a CROSS APPLY (SELECT v=a.x.query('for $r in //r[position()<=sql:variable("@i")] return xs:string($r)')) b /* 第4个'-'在'a-Gb-Kc-d-Ie'中出现的位置是10 */
CTE的实现方法:declare @s varchar(20)set @s = 'a-Gb-Kc-d-Ie' ;with cte as ( select 1 as id union all select id = id + 1 from cte where id <=len(@s) ) ,cte2 as( select distinct charindex('-',col,id) as pos from( select @s as col )a cross join cte ) ,cte3 as( select pos, row_num = row_number() over(order by pos) from cte2 where pos > 0 )select pos from cte3 where row_num = 4 --第几次 /** 4 **/
要用XML处理的话,借用FC的数据,可以简化成这样: DECLARE @s VARCHAR(100) SET @s='a-Gb-Kc-d-Ie' DECLARE @i INT DECLARE @span VARCHAR(1) SET @span = '-'SELECT @i = 4, @i = @i + 1 SELECT charindex(x.value('r[position()=sql:variable("@i")][1]','varchar(10)'),@s) - 1 FROM (SELECT x=CAST('<r>' + REPLACE(@s,@span,'</r><r>') + '</r>' AS XML)) a
自己写个函数吧 创建函数 create function instr(@str1 varchar(200),@str2 varchar(200)) returns tinyint as begin declare @i tinyint; declare @num tinyint; set @num=0 set @i=1 while @i <len(@str1) if substring(@str1,@i,1) = @str2 begin set @num=@num+1 set @i=@i+1 end else set @i=@i+1 return @num end 调用函数 exec essye.dbo.instr ('abac','a') select essys.dbo.instr ('abavaaa','a') num
DECLARE @s VARCHAR(100) SET @s='a-Ie-Kc-d-Ie--' DECLARE @i INT DECLARE @span VARCHAR(1) SET @span = '-' SET @i = 6SELECT col.value('string-length(.)','varchar(max)') + @i FROM( SELECT col = x.query('r[position()<=sql:variable("@i")]/text()') FROM (SELECT x=CAST('<r>' + REPLACE(@s,@span,'</r><r>') + '</r>' AS XML)) a )a/** 14 **/这个应该才比较准确
declare @s varchar(20)set @s = 'a-Gb-Kc-d-Ie'select top 30 id = identity(int,1,1) into #
from sys.objects --2000的话,替换为sysobjectsselect
max(charindex('-',col,id)) as pos
from(
select @s as col
)a
cross join #drop table #/**
10
**/
SET @s='a-Gb-Kc-d-Ie'
DECLARE @i INT
DECLARE @span VARCHAR(1)
SET @span = '-'
SET @i=4
SELECT '第' + RTRIM(@i) + '个''' + @span + '''在''' + @s + '''中出现的位置是' + RTRIM(LEN(CAST(v AS VARCHAR(100)))+1)
FROM
(SELECT x=CAST('<r>' + REPLACE(@s,@span,'</r><r>') + '</r>' AS XML)) a
CROSS APPLY
(SELECT v=a.x.query('for $r in //r[position()<=sql:variable("@i")] return xs:string($r)')) b
/*
第4个'-'在'a-Gb-Kc-d-Ie'中出现的位置是10
*/
;with cte as
(
select 1 as id
union all
select id = id + 1 from cte where id <=len(@s)
)
,cte2 as(
select
distinct charindex('-',col,id) as pos
from(
select @s as col
)a
cross join cte
)
,cte3 as(
select
pos,
row_num = row_number() over(order by pos)
from cte2
where pos > 0
)select
pos
from cte3
where row_num = 4 --第几次
/**
4
**/
DECLARE @s VARCHAR(100)
SET @s='a-Gb-Kc-d-Ie'
DECLARE @i INT
DECLARE @span VARCHAR(1)
SET @span = '-'SELECT
@i = 4,
@i = @i + 1
SELECT
charindex(x.value('r[position()=sql:variable("@i")][1]','varchar(10)'),@s) - 1
FROM
(SELECT x=CAST('<r>' + REPLACE(@s,@span,'</r><r>') + '</r>' AS XML)) a
创建函数
create function instr(@str1 varchar(200),@str2 varchar(200))
returns tinyint
as
begin
declare @i tinyint;
declare @num tinyint;
set @num=0
set @i=1
while @i <len(@str1)
if substring(@str1,@i,1) = @str2
begin
set @num=@num+1
set @i=@i+1
end
else
set @i=@i+1
return @num
end
调用函数
exec essye.dbo.instr ('abac','a')
select essys.dbo.instr ('abavaaa','a') num
SET @s='a-Ie-Kc-d-Ie--'
DECLARE @i INT
DECLARE @span VARCHAR(1)
SET @span = '-'
SET @i = 6SELECT
col.value('string-length(.)','varchar(max)') + @i
FROM(
SELECT
col = x.query('r[position()<=sql:variable("@i")]/text()')
FROM
(SELECT x=CAST('<r>' + REPLACE(@s,@span,'</r><r>') + '</r>' AS XML)) a
)a/**
14
**/这个应该才比较准确