Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Practical Uses of PatIndex Expand / Collapse
Author
Message
Posted Thursday, January 5, 2006 11:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:20 AM
Points: 1,616, Visits: 1,544
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rdavis/practicalusesofpatindex.asp



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #248565
Posted Tuesday, January 24, 2006 4:05 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 10, 2010 4:29 PM
Points: 8, Visits: 4
I just need now to find a need to use this PatIndex tool but it's good to now it exists

Very interresting !
Post #253028
Posted Monday, February 6, 2006 8:41 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711

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

 

Mark

Post #256039
Posted Monday, February 6, 2006 11:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:20 AM
Points: 1,616, Visits: 1,544
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #256108
Posted Monday, February 6, 2006 4:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,098, Visits: 3,155

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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #256205
Posted Tuesday, February 7, 2006 6:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 12:20 AM
Points: 31, Visits: 44

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.

Post #256344
Posted Tuesday, March 25, 2008 6:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 28, 2013 12:32 PM
Points: 7, Visits: 126
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
Post #474446
Posted Wednesday, March 26, 2008 9:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:20 AM
Points: 1,616, Visits: 1,544
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #474848
Posted Sunday, September 7, 2008 9:24 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 7, 2008 9:26 PM
Points: 1, Visits: 2
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.
Post #565198
Posted Thursday, March 31, 2011 5:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,163, Visits: 2,191
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.)
Post #1087188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse