Patindex

  • Manie Verster

    SSCertifiable

    Points: 7022

    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

    Grasshopper

    Points: 21

    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

    SSCrazy

    Points: 2087

    "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

    SSC Enthusiast

    Points: 153

    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

    Ten Centuries

    Points: 1024

    Yes, 6 it is.

  • jcrawf02

    SSC-Insane

    Points: 24198

    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:November:December'

    -- 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[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "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

    SSC Journeyman

    Points: 96

    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

    SSCrazy

    Points: 2354

    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?

    SSChasing Mays

    Points: 631

    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

    ----------------------

  • SqlOnMyMind

    SSCertifiable

    Points: 5049

    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.)

  • DPhillips-731960

    Hall of Fame

    Points: 3904

    Thanks for the post Manie! Maybe there are better ways, maybe not. Either way, the discussion is useful.

  • Wayne West

    SSC-Insane

    Points: 22586

    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.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Wayne West

    SSC-Insane

    Points: 22586

    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.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Jeff Moden

    SSC Guru

    Points: 996655

    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...

    [font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font]

    http://www.sqlservercentral.com/articles/TSQL/62867/

    [font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font]

    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.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Manie Verster

    SSCertifiable

    Points: 7022

    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)

Viewing 15 posts - 1 through 15 (of 52 total)

You must be logged in to reply to this topic. Login to reply