Usage of PatIndex in SQL Server

Introduction

Patindex is a SQL Server function which returns the first occurrence of the location of a pattern in a string. The pattern search is not case sensitive. It is very useful in many situations. In this article, we will look into some of the usages of this function.

Using the code

CASE 1: Remove Leading Zeros using PATINDEX

In this scenario, our objective is to remove the Leading Zeros from a string using PATINDEX

Let us look into the below example.

DECLARE @somevalue VARCHAR(50) = '00001234590789005'; --set the initial value

DECLARE @nonZeroPosition INT -- variable to store the occurance of first non zero value position

SELECT @nonZeroPosition = PATINDEX('%[^0]%',@somevalue) -- This pattern ensures the search position should encounter a non-zero value in the entire string 

SELECT RemoveLeadingZeros = SUBSTRING(@somevalue,@nonZeroPosition,(LEN(@somevalue)-(@nonZeroPosition - 1))) --Extract the non-zero values.

In the above example we have formed a pattern '%[^0]%' that ensures the search position should encounter a non-zero value in the entire string.

The line

SELECT @nonZeroPosition = PATINDEX('%[^0]%',@somevalue)

returns the value 5 which indicates that the first non-zero value is at position 5.

Once we have this information,the next step is to use the Substring function to extract the rest of the sub-string values from the 5th position.

Result

 
RemoveLeadingZeros
----------------------
1234590789005

CASE 2: Remove Leading Zeros and Alphabetic Characters using PATINDEX

In this scenario, our objective is to remove the Leading Zeros and Alphabetic Characters from a string using PATINDEX

Let us look into the below example.

DECLARE @somevalue VARCHAR(50) = '0000abcdef1234590789005'; --set the initial value  

DECLARE @nonZeroPosition INT -- variable to store the occurrence of first non zero value position

SELECT @nonZeroPosition = PATINDEX('%[^[0a-z]%',@somevalue) -- This pattern ensures the search position should encounter a non-zero value in the entire string 

SELECT RemoveLeadingZerosAndAlphabets = SUBSTRING(@somevalue,@nonZeroPosition,(LEN(@somevalue)-(@nonZeroPosition - 1))) --Extract the needed substring.

In the above example we have formed a pattern '%[^[0a-z]%' that ensures the search position should encounter a non-zero value in the entire string. It must also leave all the leading zeros and alphabets.

The line

SELECT @nonZeroPosition = PATINDEX('%[^[0a-z]%',@somevalue)

returns the value 5 which indicates that the first non-zero value is at position 11.

Once we have this information,the next step is to use the Substring function to extract the rest of the substring values from the 11th position.

Result

 
RemoveLeadingZerosAndAlphabets
----------------------
1234590789005

CASE 3: Remove Decimals using PATINDEX

In this scenario, our objective is to remove the decimals from the string using PATINDEX

Let us look into the below example.

DECLARE @somevalue VARCHAR(50) = '123459078.9005'; --set the initial value

DECLARE @nonZeroPosition INT -- variable to store the occurrence of first non zero value position

SELECT @nonZeroPosition = PATINDEX('%[[.]%',@somevalue) --Ensures the occurrence of a decimal

SELECT ValueBeforeDecimal = LEFT(@somevalue,@nonZeroPosition-1)

The above code also follows the same pattern as for the earlier ones. It finds the occurrence of the first decimal position and by using the LEFT function we have extracted the required substring.

There are however, some alternative approach for the above solution which are presented here

Alternative 1:

DECLARE @somevalue VARCHAR(50) = '123459078.9005'; --set the initial value

DECLARE @nonZeroPosition INT -- variable to store the occurrence of first non zero value position

SELECT @nonZeroPosition = PATINDEX('%[[.]%',@somevalue)

SELECT ValueBeforeDecimal = SUBSTRING(@somevalue,0,@nonZeroPosition)

Alternative 2:

DECLARE @somevalue VARCHAR(50) = '123459078.9005'; --set the initial value

DECLARE @nonZeroPosition INT -- variable to store the occurrence of first non zero value position

SELECT @nonZeroPosition = PATINDEX('%[[.]%',@somevalue)

SELECT ValueBeforeDecimal =STUFF(@somevalue,@nonZeroPosition,(LEN(@somevalue) - @nonZeroPosition)+1 ,'')

Alternative 3:

DECLARE @somevalue VARCHAR(50) = '123459078.9005'; --set the initial value

DECLARE @nonZeroPosition INT -- variable to store the occurrence of first non zero value position

SELECT @nonZeroPosition = PATINDEX('%[[.]%',@somevalue)

SELECT ValueBeforeDecimal =REPLACE(@somevalue,RIGHT(@somevalue,(LEN(@somevalue) - @nonZeroPosition)+1),'')

Alternative 4:

DECLARE @somevalue VARCHAR(50) = '123459078.9005'; --set the initial value

SELECT ValueBeforeDecimal =PARSENAME(@somevalue, 2)

Result

 
ValueBeforeDecimal
----------------------
123459078

CASE 4: Remove Leading Zeros and decimals using PATINDEX

In this case the objective is to remove Leading Zeros and DOTS using PATINDEX

DECLARE @somevalue VARCHAR(50) = '00001234590789005.9005'; --set the initial value

DECLARE @dotPosition INT -- variable to store the occurrence of dot position
DECLARE @nonZeroPosition INT -- variable to store the occurrence of first non zero value position
DECLARE @valueBeforeDecimal VARCHAR(50) -- variable to store values before decimals

SELECT @dotPosition = PATINDEX('%[[.]%',@somevalue)

SELECT @valueBeforeDecimal = LEFT(@somevalue,@dotPosition-1) --Extract non-decimal part

SELECT @nonZeroPosition = PATINDEX('%[^0]%',@valueBeforeDecimal)

SELECT ExtractedValue = SUBSTRING(@valueBeforeDecimal,@nonZeroPosition,(LEN(@valueBeforeDecimal)-(@nonZeroPosition - 1))) --remove leading zero(s)

In this case, we have first extracted the non-decimal values and then from that we have removed the Leading Zeros.

Result

 
ExtractedValue
----------------------
1234590789005

Conclusion

Hope this article will be helpful for learning the usage of PatIndex. Thanks for reading.