How to Search Using PATINDEX function...Plz Help

[YogeshJainISC]

Hi,

How to search the last occurance of one String in to another string..Using function PATINDEX() in a column of data type Text..

plz Give some solution i really need it ...



Thanks Yogesh

[nosepicker]

For something like this, you'll probably need to use a function, and have it use CHARINDEX instead of PATINDEX:

--Example table and data
create table tmp (
col1 text null,
col2 int null)

insert into tmp values ('fadslkjlkabcadsfla;sjfabc;fasdlabcaf', 1)
insert into tmp values ('fdakjlabcarekasllkj;ljlabc', 2)
insert into tmp values ('abcafabcabacabcabacabalkjaklabc;jl', 3)

--Function to find last occurence of string in text column
CREATE FUNCTION string_position (@string varchar(50), @primary_key int)
RETURNS int AS
BEGIN
DECLARE @charindex int, @start int

SET @start = 1
WHILE (SELECT CHARINDEX(@string, col1, @start) FROM tmp WHERE col2 = @primary_key) > 0
BEGIN
SELECT @charindex = CHARINDEX(@string, col1, @start)
FROM tmp
WHERE col2 = @primary_key
SET @start = @charindex + LEN(@string)
END

RETURN @charindex
END

--Use function in query
SELECT col1, dbo.string_position('abc', col2)
FROM tmp

[YogeshJainISC]

Hi nosepicker,

Thankx for ur reply , I have used charindex(),but its not working on column of type "Text" as per the requirement. Patindex is used for "Text" .

My requirement is i want to search the last occurance of one string in another string of type "text".

so plz suggest some solution for this.....


Thanks
Yogesh

[nosepicker]

Did you bother to try it? Charindex works for me on text columns.

[YogeshJainISC]

Smart people learn from the mistake,& Ur mistake is u hadn't check it above the limit of varchar..

Do one thing ,Insert the data of length more than 8000 (suppose 9786),then search the string which is on position more than 8000 ( Suppose 9000)
then check ur code..

Actually this function work for varchar(8000) not more than that...

[nosepicker]

You're right, my mistake. I didn't check for longer strings - I apologize. However, since you're the one asking for help, and I've volunteered my time to try to help you, you should be a little more respectful and mindful of that, if you want me to continue to give up my free time to find a solution for you.

[YogeshJainISC]

Hi,
I apologize if i hert u,this is not my intention ,i just want u to take this thing little seriously, i tryied lote of thing ,i really appreciate ur afforts..

But i am stil searching for a better solution..

[mkeast]

Here's a simple example that search a text column for the occurrence of a string. It outputs the starting position of the search string within the text column, a verifies that by printing out a portion of the text column.

CREATE TABLE tbl
(
id int IDENTITY(1,1)
, myText text
)
GO
INSERT tbl(myText)
SELECT (Replicate('ABCDEFGHIJKLMNOPQRSTUVWXY',10) + '0123456789')
UNION ALL
SELECT (Replicate('ABCDEFGHIJKLMNOPQRSTUVWXY',20) + '0123456789')
UNION ALL
SELECT (Replicate('ABCDEFGHIJKLMNOPQRSTUVWXY',30) + '0123456789')
UNION ALL
SELECT (Replicate('ABCDEFGHIJKLMNOPQRSTUVWXY',40) + '0123456789')

DECLARE @searchFor varchar(100)
SET @searchFor = '012345'

SELECT PatIndex('%' + @searchFor + '%', myText) AS StartPosition
, Substring(myText, PatIndex('%' + @searchFor + '%', myText), Len(@searchFor)) AS SearchString
FROM tbl

DROP TABLE tbl