PatIndex is not working

Question

When I try to execute query for PATINDEX in SSMS i am getting 0 always. Can anyone please assist?

DECLARE @COL varchar(100) = '000589'
SELECT PATINDEX('5',@COL )

Answer 1

According to documentation, you must write PATINDEX('%5%', @COL ).

Maybe CHARINDEX('5', @COL ) is also suitable for your cases.

Answer 2

The syntax for PatIndex is
PATINDEX ( '%pattern%' , expression )

see the part from the documentation

pattern
Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category. pattern is limited to 8000 characters.

Refer: https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver16

see the correct sample below

Answer 3

Hi，@kkran

Welcome to Microsoft T-SQL Q&A Forum!

This is a basic syntax problem, two experts have given the correct code, normal positioning in the string, I did this:

DECLARE @col varchar(100) = '000589'  
SELECT pos=PATINDEX('%[5]%',@col )  

If you change the scenario, you need to find the character positioning of a column in a table in the database, you can also achieve this:

create tabLE #TEEEE (id INT  ,COL varchar(10))  
insert into #TEEEE values('000589'),('5012852')  
select PATINDEX('%[5]%',col )pos  
from #TEEEE  
where col like '%[5]%'  

Tip
PATINDEX is a built-in function in SQL Server. It finds the first index in the string that matches the given pattern. It does not support regular expressions; instead, it supports the same patterns as the LIKE operator. This means it can do some limited wildcard pattern matching.

Some of the obvious limitations of PATINDEX are:
1)It only supports simple mode.
2)It can only match the first occurrence of the pattern.
3)It doesn't return the matches it finds.

Best regards,
Bert Zhou