SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Patindex


Patindex

Author
Message
DPhillips-731960
DPhillips-731960
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1936 Visits: 801
Thanks for the post Manie! Maybe there are better ways, maybe not. Either way, the discussion is useful.
Wayne West
Wayne West
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9066 Visits: 3705
vibhasjog (9/11/2008)
i have a job on my server which takes data from linked server which generally takes 10-15 mins but
from past few days it takes 1 hour to complete or the job fails giving the below error

Message
Executed as user: NT AUTHORITY\SYSTEM. Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "MYSERVER". [SQLSTATE 42000] (Error 7330) OLE DB provider "SQLNCLI" for linked server "MYSERVER" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.

vibhasjog, this isn't really the place to post a problem/question, this is the place for comments/replies to Manie's article on Patindex. You'd get more appropriate replies by posting under the Programming/Connection forum where people who know more about your type of error hang out. Those people might not read this article and would otherwise never see your question.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Wayne West
Wayne West
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9066 Visits: 3705
Manie, good article! Myself, I don't think that I've ever used patindex, but I use the other string functions heavily. It's amazing how many people don't do string manipulation: I recently taught a group of skilled programmers an Intro to SQL Server/Database Theory class (they were Cobol & Oracle users mainly) and they were amazed what I could do with basic string manipulation.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)

Group: General Forum Members
Points: 379457 Visits: 42943
Manie, great primer on PatIndex!

There is hundreds of other ways to use these functions and the way I used here is most probably not the best way so, if anyone out there have any suggestions then I'm always open to new ideas. Do a WHILE loop to strip the months and insert them into one column with multiple rows.


Ok... here's a suggestion... I'd recommend NOT using a WHILE loop to parse rows. Here's why...

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
http://www.sqlservercentral.com/articles/TSQL/62867/

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays
http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Manie Verster
Manie Verster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1044
martin.moss (9/11/2008)
The PATINDEX function provide you with the exact position of a pattern in the expression e.g. if you want to know where "f" is in the expression "abcdef" then the answer will be 5 using the PATINDEX function.


Returns 6 doesnt it?



So sorry, that must have been a typo but I'm sure you get the gist of what I mean.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Manie Verster
Manie Verster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1044
Jeff Moden (9/11/2008)
Manie, great primer on PatIndex!

There is hundreds of other ways to use these functions and the way I used here is most probably not the best way so, if anyone out there have any suggestions then I'm always open to new ideas. Do a WHILE loop to strip the months and insert them into one column with multiple rows.


Ok... here's a suggestion... I'd recommend NOT using a WHILE loop to parse rows. Here's why...

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
http://www.sqlservercentral.com/articles/TSQL/62867/

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays
http://www.sqlservercentral.com/articles/T-SQL/63003/


Wayne and Jeff and all the others with positive feedback,

Thanks a lot for your positive feedback. This morning when I opened my e-mail and saw that my article wish published, my heart was in my throat. This is my first ever article and I am feeling very proud. It makes me feel good that guys like you gives such nice feedback. Jeff, I have taken note of your suggestion and will definitely consider using it.
Thanks to SCC for this opportunity.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Manie Verster
Manie Verster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3151 Visits: 1044
jcrawf02 (9/11/2008)
Your solution works if the original set is small, like your example of 'January:February:March', but as you pointed out, gets ugly the larger your string gets.

Two things -
1) use CHARINDEX, not PATINDEX, because you can specify a starting point in CHARINDEX. Then it's just a matter of finding the previous ':' and going from there. By the way, you don't actually need the '%' in your search string when using CHARINDEX, you can just search for CHARINDEX(':',monthname)
2) you can use a loop to find these values (see below) but the best way to do this, especially if you have either an undetermined length or a large value, is to use a Tally or Numbers table. See Jeff Moden's article on Tally tables here:http://www.sqlservercentral.com/articles/TSQL/62867/, including splitting a string.

Here's a sample loop that will accomplish this for the year's worth of months, not nearly as efficient as a tally table, but does the job.
IF OBJECT_ID('tempdb..#concatmonth') IS NOT NULL BEGIN DROP TABLE #concatmonth END

create table #concatmonth(monthname varchar(255))
insert #concatmonth(monthname)select 'January:February:March:April:May:June:July:August:September:October:NovemberBigGrinecember'

-- create a table to store results in
IF OBJECT_ID('tempdb..#MyHead') IS NOT NULL BEGIN DROP TABLE #MyHead END
create table #MyHead (PK int identity(1,1),
monthnames varchar(255))
--original solution
select monthname, LEFT(monthname,PATINDEX('%:%',monthname)-1) as firstmonth,
SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)) secondpart,
LEFT(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)),PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)))-1) secondmonth,
SUBSTRING(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)),PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN( monthname)))+1,LEN(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)))) thirdmonth
from #concatmonth

--new solution
DECLARE @currentLocation int, @currentString varchar(255)
SET @currentLocation = 1
SET @currentString = (SELECT monthname FROM #concatmonth)

WHILE charindex(':',@currentString,@currentLocation)>0
BEGIN
INSERT INTO #MyHead
SELECT substring(@currentString,1,charindex(':',@currentString,@currentLocation)-1)

SELECT @currentString = substring(@currentString,charindex(':',@currentString,@currentLocation)+1,len(@currentString)-charindex(':',@currentString,@currentLocation)+1)
END
-- one last time to catch last iteration without ending ':'
INSERT INTO #myHead
SELECT @currentString

SELECT * FROM #MyHead ORDER BY PK




You are quite right jcrawf02. I decided test your theory about patindex and charindex and also include a check on the like as well. I created a query to count the number of rows (+-29000) with like, patindex and charindex and I must say that the difference in duration was very visible. The like and the patindex both gave me 33 milliseconds where the charindex gave me 13 milliseconds. I was a bit apprehensive about your post at first but now I am thankful. I have learned how to tune my stored procedure thanks to a part of Jeff Moden's Tally table method and also not to take all functions and methods at face value.

Thanks a lot.TongueTongueTongueTongueWinkWinkWink

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 1951
Thanks for testing and comparing the performance of PATINDEX, LIKE and CHARINDEX! You've inspired me to test some of my procedures. Looks like they could benefit from using CHARINDEX instead of LIKE.
jcrawf02
jcrawf02
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8038 Visits: 19324
Carla Wilson (9/12/2008)
Thanks for testing and comparing the performance of PATINDEX, LIKE and CHARINDEX! You've inspired me to test some of my procedures. Looks like they could benefit from using CHARINDEX instead of LIKE.


I'll second that 'Thank you'; my lazy butt hadn't even gone far enough to compare them, I just didn't like looking at all of the code as the string got longer. BigGrin

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 1951
Just wanted to let you know I tested the three different string functions, LIKE, CHARINDEX and PATINDEX, on a script I have to do data cleansing on name and address fields.

Example of part of the script:
UPDATE TestNameAddress
SET LoadType = 'T'
WHERE
CHARINDEX('TEST',firstname) > 0
or CHARINDEX('TEST',middlename) > 0
or CHARINDEX('TEST',lastname) > 0
...
UPDATE TestNameAddress
SET City = null
WHERE
CHARINDEX('XX',City) = 1
or CHARINDEX('##',City) = 1
or CHARINDEX('**',City) = 1)
...
(You get the idea)

On a test table with 21,000 rows, and values loaded into each column that would match the criteria, here are the average times (with consistent results from trial to trial).
LIKE: 2736 millisec.
CHARINDEX: 2780 millisec.
PATINDEX: 2912 millisec.

To remove the impact of the actual UPDATE on the times, I reran each script with 21,000 rows that would not match the criteria.
LIKE: 780 millisec.
CHARINDEX: 810 millisec.
PATINDEX: 960 millisec.

I was surprised to see the performance of LIKE, since I have heard it is not the most efficient.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search