34,590
社区成员
发帖
与我相关
我的任务
分享
declare @t table(id int ,s nvarchar(1000))
insert @t
select 1,'#@#5463#@#1dfadf#@#eqQQW#@##@##@##@##@##@##@#dsfasdf32#@##@##@##@##@##@##@#'
union select 2,'#@#斯柯达#@#1d中午的更fadf#@#e阿斯蒂芬W#@##@##@##@##@#2345#@##@##@##@##@##@##@#er'
union select 3,'#@#5证券不#@#本f#@#e5W#@##@##@##@##@##@##@##@#2345#@##@##@##@##@##@#'
----------
select *
from (
select id,number + 2 number ,w = ROW_NUMBER() over(PARTITION by id order by id,number)
from @t a join (select * from master.dbo.spt_values where type = 'P') b on substring( s, number ,3) = '#@#'
) t
where w = 5
/* 结果
(3 row(s) affected)
id number w
----------- ----------- --------------------
1 30 5
2 34 5
3 24 5
(3 row(s) affected)
*/
--测试数据
IF OBJECT_ID('temp_db..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
txt VARCHAR(200)
)
INSERT INTO #tab
SELECT '#@#5463#@#1dfadf#@#eqQQW#@##@##@##@##@##@##@#dsfasdf32#@##@##@##@##@##@##@#'
UNION all
select '#@#斯柯达#@#1d中午的更fadf#@#e阿斯蒂芬W#@##@##@##@##@#2345#@##@##@##@##@##@##@#er'
UNION all
select '#@#5证券不#@#本f#@#e5W#@##@##@##@##@##@##@##@#2345#@##@##@##@##@##@#'
--测试数据结束
/*
* 创建一个函数fn_find
* @find要查找的字符
* @str在哪个字符串里查找
* @n第几次出现的索引
* */
create function fn_find(@find varchar(8000), @str varchar(8000), @n smallint)
returns int
as
begin
if @n < 1 return (0)
declare @start smallint, @count smallint, @index smallint, @len smallint
set @index = charindex(@find, @str)
if @index = 0 return (0)
else select @count = 1, @len = len(@find)
while @index > 0 and @count < @n
begin
set @start = @index + @len
select @index = charindex(@find, @str, @start), @count = @count + 1
END
--SET @index=@index+LEN(@find)-1
if @count < @n set @index = 0
return (@index)
end
--调用上面的函数
SELECT * ,dbo.fn_find('#@#',txt,5) AS [INDEX] FROM #tab
--有了以上函数,你想查第几次就查第几次出现的索引。
--声明一点,以上字符查询出来的正确索引应该是28,32,22,之所以你的会为30,34,24。
--是因为你把'#@#'里的后两个字符也算进去了,如果你需要得到你说的30,34,24那样,就把上面函数中注释的那一条取消注释
txt INDEX
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
#@#5463#@#1dfadf#@#eqQQW#@##@##@##@##@##@##@#dsfasdf32#@##@##@##@##@##@##@# 30
#@#斯柯达#@#1d中午的更fadf#@#e阿斯蒂芬W#@##@##@##@##@#2345#@##@##@##@##@##@##@#er 34
#@#5证券不#@#本f#@#e5W#@##@##@##@##@##@##@##@#2345#@##@##@##@##@##@# 24
(3 行受影响)
if not object_id(N'Tempdb..#Tmp_Data') is null
drop table #Tmp_Data
Go
Create table #Tmp_Data(List_ID int,myStr nvarchar(100))
Insert #Tmp_Data
select 1,N'#@#5463#@#1dfadf#@#eqQQW#@##@##@##@##@##@##@#dsfasdf32#@##@##@##@##@##@##@#' union
Select 2,N'#@#斯柯达#@#1d中午的更fadf#@#e阿斯蒂芬W#@##@##@##@##@#2345#@##@##@##@##@##@##@#er' union
Select 3,N'#@#5证券不#@#本f#@#e5W#@##@##@##@##@##@##@##@#2345#@##@##@##@##@##@#'
Go
Select List_ID,(Sum(Len(Col))+15) as Index_No
From (
Select List_ID,col,Row_No=ROW_NUMBER() over (Partition by List_ID Order By List_ID)
From (
Select List_ID,t.c.value('.','nvarchar(20)') AS col
From (SELECT List_ID,CAST('<x>'+REPLACE(myStr,'#@#','</x><x>')+'</x>' AS XML ).query('.') AS name From #Tmp_Data) AS a
CROSS APPLY a.name.nodes('/x') T(c)
) a
) a
Where Row_No<=5
Group By List_ID