Post reply

Practical Uses of PatIndex

  • January 5, 2006 at 11:22 am

    #166399

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rdavis/practicalusesofpatindex.asp

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • January 24, 2006 at 4:05 am

    #616615

    I just need now to find a need to use this PatIndex tool but it's good to now it exists

    Very interresting !

  • February 6, 2006 at 8:41 am

    #618995

    I Wonder if there if there is any difference in performance?  Anyone try any testing?

     

    Mark

  • February 6, 2006 at 11:37 am

    #619047

    Difference between using PatIndex and CharIndex, you mean? Good question. I have not done any such testing, but it might make for a good follow up article.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • February 6, 2006 at 4:00 pm

    #619125

    Don't see any need for the "IF" at the start of the function.  I wrote an almost identical function for this purpose but with just the WHILE.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • February 7, 2006 at 6:33 am

    #619229

    One of the biggest differences is in application to the text fields. The charindex is not working (only for first 8000) while the patindex is. It returns the position while the like operation doesn't.

  • March 25, 2008 at 6:06 pm

    #793806

    It is a very good post. However, it does not check for the valid range of integer number. For example: 3147483647 will result in true, however, according to sql server, this is not a valid integer because it exceeds the range of integers. ie -2,147,483,648 through 2,147,483,647.

    It also fails to capture comma separated integers, eg 1,234. This is a valid integer, but the UDF returns false.

    thanks,

    Mashiku

  • March 26, 2008 at 9:32 am

    #794135

    Thanks for the comments!! The UDF wasn't intended to be an all encompassing way to determine if a number could be converted to int. The UDF was merely a way to demonstrate how to use PatIndex effectively.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • September 7, 2008 at 9:24 pm

    #868279

    Here is a way that I recently found using PATINDEX useful:

    http://codeslammer.wordpress.com/2008/09/02/sql-server-string-manipulation-removing-non-numeric-characters/

    I have a column that contains an ID number, but there may be text before and/or after it. The text is often similar, but it can not be guaranteed that there will be text or not. However there should only be one number and this is the number I am trying to extract.

    For example, if you had a column with values such as:

    "Serial Number: 3078203984-02"

    "SN#472038572289"

    "Serial No. 02-28349-2074"

    and you just wanted to strip out the unique serial numbers, this is one option to do it.

  • March 31, 2011 at 5:25 pm

    #1306254

    In your article you say:

    Since this article is focusing on practical uses of PatIndex, I will only be checking to see if the string value contains digits only with the exception of allowing it to begin with a negative sign. I will not include checking to see if the value is within the allowable range of the Int data type.

    But even in your test:

    Select IsNumeric('-4'), PatIndex('%[^0-9]%', '-4'), dbo.fnIsInt('-4')

    It returned 0, so it wasn't doing what you said it was supposed to. (It isn't as simple as just two PatIndex calls.)

Viewing 10 posts - 1 through 9 (of 9 total)

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