SQL查找某个字符串第N次出现的位置的函数(转帖)

01.if exists(select 1 from sysobjects where name='char_index')
02. 03.drop function char_index
04. 05.
06. 07.create function char_index(@string varchar(8000),@char varchar(10),@index smallint)
08. 09.--@string:待查找字符串，@index:查找位置
10. 11.returns smallint
12. 13.as
14. 15.begin
16. 17. declare
18. 19. @i tinyint,--当前找到第@i个
20. 21. @position tinyint--所在位置
22. 23. set @position=@index;
24. 25. set @i=0;
26. 27. while charindex(@char,@string,@position)>0
28. 29. begin
30. 31. set @position=charindex(@char,@string,@position)+1;
32. 33. set @i=@i+1;
34. 35. if @i=@index
36. 37. begin
38. 39. return @position-1;
40. 41. end
42. 43. end
44. 45. return 0;--0表示未找到
46. 47.end
48. 49.
50. 51.select dbo.char_index('sdf_dsf_dfgdg_ertr_erte','f_',2)
if exists(select 1 from sysobjects where name='char_index')drop function char_index create function char_index(@string varchar(8000),@char varchar(10),@index smallint)--@string:待查找字符串，@index:查找位置returns smallintasbegin declare @i tinyint,--当前找到第@i个 @position tinyint--所在位置 set @position=@index; set @i=0; while charindex(@char,@string,@position)>0 begin set @position=charindex(@char,@string,@position)+1; set @i=@i+1; if @i=@index begin return @position-1; end end return 0;--0表示未找到end select dbo.char_index('sdf_dsf_dfgdg_ertr_erte','f_',2) [SQL] view plaincopyprint?01.--如何查找某个字符串第N次出现的位置,
02. 03.--比如:字符串"sdf_dsf_dfgdg_ertr_erte",要查找"f_"第二次出现的位置
04. 05.if object_id('f_findstr') is not null
06. 07.drop function f_findstr
08. 09.go
10. 11.create function f_findstr(@s varchar(8000),@find varchar(10),@index int)
12. 13.returns int
14. 15.as
16. 17.begin
18. 19.declare @startindex int
20. 21.set @startindex=0
22. 23.while @index>0
24. 25.begin
26. 27.if charindex(@find,@s,@startindex)>0
28. 29.set @startindex=charindex(@find,@s,@startindex+1)
30. 31.set @index=@index-1
32. 33.end
34. 35.return @startindex
36. 37.end
38. 39.go
40. 41.select dbo.f_findstr('sdf_dsf_dfgdg_ertr_erte','f_',2)
--如何查找某个字符串第N次出现的位置,--比如:字符串"sdf_dsf_dfgdg_ertr_erte",要查找"f_"第二次出现的位置if object_id('f_findstr') is not nulldrop function f_findstrgocreate function f_findstr(@s varchar(8000),@find varchar(10),@index int)returns intasbegindeclare @startindex intset @startindex=0while @index>0beginif charindex(@find,@s,@startindex)>0set @startindex=charindex(@find,@s,@startindex+1)set @index=@index-1endreturn @startindexendgoselect dbo.f_findstr('sdf_dsf_dfgdg_ertr_erte','f_',2) [SQL] view plaincopyprint?01.use test
02. 03.go
04. 05.if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F_Str]') and xtype in (N'FN', N'IF', N'TF'))
06. 07.drop function [dbo].[F_Str]
08. 09.GO
10. 11.
12. 13.CREATE FUNCTION dbo.F_Str(
14. 15.@s varchar(8000),
16. 17.@pos int,
18. 19.@split varchar(10)
20. 21.)RETURNS int
22. 23.AS
24. 25.BEGIN
26. 27. IF @s IS NULL RETURN(NULL)
28. 29. DECLARE @splitlen int,@i int
30. 31. SELECT @splitlen=LEN(@split+'a')-2,@i=0
32. 33. WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
34. 35. SELECT @pos=@pos-1,@i=@i+CHARINDEX(@split,@s+@split)+@splitlen,
36. 37. @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
38. 39. RETURN(@i+CHARINDEX(@split,@s+@split))
40. 41.END
42. 43.GO
44. 45.declare @s nvarchar(100)
46. 47.select @s='sdf_dsf_dfgdg_ertr_erte'
48. 49.select dbo.F_Str(@s,2,'f_')
50. 51.
52. 53.
54. 55.-----------
56. 57.7
58. 59.
60. 61.(1 行受影响) use testgoif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F_Str]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[F_Str]GOCREATE FUNCTION dbo.F_Str(@s varchar(8000), @pos int, @split varchar(10) )RETURNS intASBEGIN IF @s IS NULL RETURN(NULL) DECLARE @splitlen int,@i int SELECT @splitlen=LEN(@split+'a')-2,@i=0 WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0 SELECT @pos=@pos-1,@i=@i+CHARINDEX(@split,@s+@split)+@splitlen, @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'') RETURN(@i+CHARINDEX(@split,@s+@split))ENDGOdeclare @s nvarchar(100)select @s='sdf_dsf_dfgdg_ertr_erte'select dbo.F_Str(@s,2,'f_')-----------7(1 行受影响)方法4[SQL] view plaincopyprint?01.if exists(select 1 from sysobjects where name='char_index')
02. 03.drop function char_index
04. 05.go
06. 07.create function char_index(@string varchar(8000),@char varchar(10),@index smallint)
08. 09.--@string:待查找字符串，@index:查找位置
10. 11.returns smallint
12. 13.as
14. 15.begin
16. 17. declare
18. 19. @i int,--当前找到第@i个
20. 21. @position int--所在位置
22. 23. set @position=1;
24. 25. set @i=0;
26. 27. while charindex(@char,@string,@position)>0
28. 29. begin
30. 31. set @position=charindex(@char,@string,@position)+1;
32. 33. set @i=@i+1;
34. 35. if @i=@index
36. 37. begin
38. 39. return @position-1;
40. 41. end
42. 43. end
44. 45. return 0;--0表示未找到
46. 47.end
48. 49.go