AT and RAT UDFs - Search a character expression for the occurrence of another character expression

Submitted by Sergey on January 14, 2009 - 18:48 UTC
topic: 
sql
tsql
udf
mssql
code

T-SQL has CHARINDEX and PATINDEX functions that return the position of one character expression in another. The ufn_AT and ufn_RAT functions add the ability to search for specified occurrences and to search from right to left.

They both have the same syntax: 




1
2
3
4
5
6
7





dbo.ufn_AT(SearchFor, SearchIn, Occurrence)
dbo.ufn_RAT(SearchFor, SearchIn, Occurrence)
  where
    SearchFor - The character expression to search for.
    SearchIn - The character expression to search in.
    Occurrence - Which occurrence to return (default is 1).

$SAMPLECODE$





 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73






-- ufn_AT ---
IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_AT')
	DROP FUNCTION ufn_AT
go
/* Returns the position of one character expression in another
 Parameters:
	@SearchFor  - The character expression to search for
	@SearchIn   - The character expression to search in 
	@Occurrence - Specifies which occurrence of @SearchFor in @SearchIn is returned (1st occurrence is 1)
*/

CREATE FUNCTION ufn_AT 
	(@SearchFor varchar(1000), 
	 @SearchIn varchar(8000), 
	 @Occurrence int = 1) 
RETURNS int
AS
BEGIN
	DECLARE @Pos int, @CurrentOccurrence int
	SET @CurrentOccurrence = 0
	SET @Pos = 0
	WHILE @CurrentOccurrence < @Occurrence
	BEGIN
		SET @Pos = CHARINDEX(@SearchFor, @SearchIn, @Pos+1)
		IF @Pos = 0 BREAK
		SET @CurrentOccurrence = @CurrentOccurrence + 1
	END
	RETURN @Pos
END
go
------------------------------------------------------------------------------------

-- ufn_RAT --

IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_RAT')
	DROP FUNCTION ufn_RAT
go
/* Returns the position of one character expression in another 
		starting from the end of the string
 Parameters:
	@SearchFor  - The character expression to search for
	@SearchIn   - The character expression to search in
	@Occurrence - Which occurrence of @SearchFor in @SearchIn is returned (default is 1)
*/

CREATE FUNCTION ufn_RAT 
	(@SearchFor varchar(1000), 
	 @SearchIn varchar(8000), 
	 @Occurrence int = 1) 
RETURNS int
AS
BEGIN
	DECLARE @Pos int, @CurrentOccurrence int
	-- Search from the right to left by reversing both strings
	SET @SearchFor = REVERSE(@SearchFor)
	SET @SearchIn = REVERSE(@SearchIn)
	SET @CurrentOccurrence = 0
	SET @Pos = 0
	WHILE @CurrentOccurrence < @Occurrence
	BEGIN
		SET @Pos = CHARINDEX(@SearchFor, @SearchIn, @Pos+1)
		IF @Pos = 0 BREAK
		SET @CurrentOccurrence = @CurrentOccurrence + 1
	END
	IF @Pos > 0
		-- Convert to the position from the left
		SET @Pos = DATALENGTH(@SearchIn) - @Pos + 1 - DATALENGTH(@SearchFor) + 1
	RETURN @Pos
END
go

Examples of use 




1
2
3
4
5






SELECT dbo.ufn_AT('Long', 'Some Long Long Long text', DEFAULT)
SELECT dbo.ufn_RAT('Long', 'Some Long Long Long text', 2)

Comments