-
How to Search Using PATINDEX function...Plz Help
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
-
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
-
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
-
Did you bother to try it? Charindex works for me on text columns.
-
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...
-
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.
-
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..
-
Simple example using PatIndex()
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|