CODEWITHSUNDEEP
sqlsql-servert-sqlsyntax
user3482471
user3482471

Reputation: 227

Simple Explanation for PATINDEX

I have have been reading up on PATINDEX attempting to understand what and why. I understand the when using the wildcards it will return an INT as to where that character(s) appears/starts. So:

SELECT PATINDEX('%b%', '123b') -- returns 4

However I am looking to see if someone can explain the reason as to why you would use this in a simple(ish) way. I have read some other forums but it just is not sinking in to be honest.

Upvotes: 1

Views: 5987

Answers (3)

asontu
asontu

Reputation: 4659

Are you asking for realistic use-cases? I can think of two, real-life use-cases that I've had at work where PATINDEX() was my best option.

I had to import a text-file and parse it for INSERT INTO later on. But these files sometimes had numbers in this format: 00000-59. If you try CAST('00000-59' AS INT) you'll get an error. So I needed code that would parse 00000-59 to -59 but also 00000159 to 159 etc. The - could be anywhere, or it could simply not be there at all. This is what I did:

DECLARE @my_var VARCHAR(255) = '00000-59', @my_int INT

SET @my_var = STUFF(@my_var, 1, PATINDEX('%[^0]%', @my_var)-1, '')
SET @my_int = CAST(@my_var AS INT)

[^0] in this case means "any character that isn't a 0". So PATINDEX() tells me when the 0's end, regardless of whether that's because of a - or a number.

The second use-case I've had was checking whether an IBAN number was correct. In order to do that, any letters in the IBAN need to be changed to a corresponding number (A=10, B=11, etc...). I did something like this (incomplete but you get the idea):

SET @i = PATINDEX('%[^0-9]%', @IBAN)
WHILE @i <> 0 BEGIN
  SET @num = UNICODE(SUBSTRING(@IBAN, @i, 1))-55
  SET @IBAN = STUFF(@IBAN, @i, 1, CAST(@num AS VARCHAR(2))
  SET @i = PATINDEX('%[^0-9]%', @IBAN)
END

So again, I'm not concerned with finding exactly the letter A or B etc. I'm just finding anything that isn't a number and converting it.

Upvotes: 1

Jodrell
Jodrell

Reputation: 35726

Quoted from PATINDEX (Transact-SQL)

The following example uses % and _ wildcards to find the position at which the pattern 'en', followed by any one character and 'ure' starts in the specified string (index starts at 1):

SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');

Here is the result set.

8

You'd use the PATINDEX function when you want to know at which character position a pattern begins in an expression of a valid text or character data type.

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272376

PATINDEX is roughly equivalent to CHARINDEX except that it returns the position of a pattern instead of single character. Examples:

Check if a string contains at least one digit:

SELECT PATINDEX('%[0-9]%', 'Hello') -- 0
SELECT PATINDEX('%[0-9]%', 'H3110') -- 2

Extract numeric portion from a string:

SELECT SUBSTRING('12345', PATINDEX('%[0-9]%', '12345'), 100) -- 12345
SELECT SUBSTRING('x2345', PATINDEX('%[0-9]%', 'x2345'), 100) -- 2345
SELECT SUBSTRING('xx345', PATINDEX('%[0-9]%', 'xx345'), 100) -- 345

Upvotes: 1

Related Questions