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


Patindex


Patindex

Author
Message
Manie Verster
Manie Verster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1829 Visits: 1022
Comments posted to this topic are about the item Patindex

:-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)
vibhasjog
vibhasjog
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 2
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.
umailedit
umailedit
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 246
"So if you want to memorize functions that you can't remember the syntaxes for, write an article for SQLServerCentral.com. "

Thats good advice.
martin.moss
martin.moss
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 82
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?
Michael.Beeby
Michael.Beeby
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 678
Yes, 6 it is.
jcrawf02
jcrawf02
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2984 Visits: 19324
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



---------------------------------------------------------
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."
kenk-601293
kenk-601293
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 59
I'd be a a little concerned about the performance implications of using this function, what's been your experience on cpu impact.
Andy Lennon
Andy Lennon
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 826
i would have been more interested to see a unique application of the PATINDEX function, rather than examples that could have more easily been CHARINDEX situations. Searching for PATINDEX('%ber%',monthname) > 0 for example.
bettername?
bettername?
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 563
I'm always splitting concatenated strings into rows/whatnot so I knocked the following function up to give me the (n)th value in a delimited string. Bung it in a loop and you get your insert-all-rows done in a few lines of mini-code... may be useful for some of you. Could substitute charindex for patindex to keep this fully on-topic I suppose!

ufn_midinstance(@string, @delimiter, @instance, @length)

@string = string to parse
@delimiter = obvious...
@instance = zero-based value in the delimiter string to return
@length = number of chars to return for the value, or use 0 to get the whole value up to the next delimiter

eg: select ufn_midinstance('hello:world:what:a:boring:example', ':', 2,0)
returns: 'what'

eg: select ufn_midinstance('hello:world:what:a:boring:example', ':', 2,2)
returns: 'wh'

Then you can wrap 'er up in a loop to insert with something like:

declare @string varchar(100)
set @string = 'hello:world:what:a:boring:example'
declare @loop int
set @loop = 0
while @loop <= len(@string) - len(replace(@string,':',''))
begin
insert into SomeTable (SomeColumn)
select dbo.ufn_midinstance(@string, ':', @loop, 0)
set @loop = @loop+1
end




-------ufn_midinstance--------
create function ufn_midinstance(@string nvarchar(4000), @delimiter char, @instance int, @length int)
returns nvarchar(4000)
as
begin

declare @i int
set @i = 0

while @i < @instance
begin

set @string = substring(@string, charindex(@delimiter, @string,1)+1, len(@string))
set @i = @i+1
end

declare @string2 nvarchar(4000)
if charindex(@delimiter, @string, 1)>=1 set @string2 = substring(@string, 1, charindex(@delimiter, @string,1)-1)
else set @string2 = @string

set @string = substring(@string, 1, @length)

declare @return nvarchar(4000)

if @length = 0 set @return = @string2 else set @return = @string

if @return = '' set @return = null
return @return

end
----------------------
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2061 Visits: 1950
Some time ago, I checked BOL to read about the PATINDEX string function, because I wanted to use a regular expression instead of CHARINDEX with a character string.
Unfortunately, the examples shown in BOL looked to me like CHARINDEX usage.
I didn't see comparison of when to use PATINDEX rather than CHARINDEX, except for a note that CHARINDEX cannot be used on text, ntext or image datatypes.

Digging deeper, I DID find the explanation of wildcards, including the use of brackets like [0-9] for "any number" or [^0-9] for "not a number".
I also learned that you could use these "regular expression"-type wildcards with CHARINDEX and LIKE, which is handy information. CHARINDEX has the added feature of being able to specify a starting position for the search.

As near as I could see, the only time you need to use PATINDEX is for text, ntext datatypes.
Does anyone know if there is an advantage to using PATINDEX?

(Furthermore, the only WHERE conditions that you can use on text columns are LIKE, IS NULL, and PATINDEX. Makes me wonder if PATINDEX was designed for use with text, ntext datatypes as opposed to char, nchar, varchar, etc.)
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