Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Patindex

By Manie Verster,

In my work I was often put before a task of stripping concatenated columns in a table and I think this was one of the lessons that was the most difficult for me to learn. The challenges I was faced with made me an (may I say) an expert in this area. I would like to discuss the PATINDEX function and with that also a couple of other that you might find very common but some of us are a bit challenged in the learning. It's like my boss always say: "It takes you a while to click (understand) but once you clicked it's there forever". The other functions I want to use with PATINDEX is LEFT and SUBSTRING and I will also use the WHERE clause. There are other functions that you can also use but this is it for today. The syntax and descriptions of the arguments are courtesy of SQL Books Online. I can't explain it like they do, but I will try to put some extra oomph in this.

PATINDEX('%'+pattern+'%', expression)

The PATINDEX function provide you with the exact position of a pattern in the expression e.g. if you want to know where "f" is in the expression "abcdef" then the answer will be 5 using the PATINDEX function.


pattern
Is a literal string. 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.

expression
Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.

LEFT(character_expression, Integer_expression)

The LEFT function results on the characters on the left side of your character_expression according to the length or integer_expression that you supplied. I your string is "abcdef" and you asked for LEFT('abcdef',3) the result would be "abc".


character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression
Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. integer_expression can be of type bigint.

SUBSTRING(expression, start, length)

The SUBSTRING function can be used to extract a part from an expression. Let's take "abcdef" as example. The SUBSTRING can be used extract for instance the "bcd" from "abcdef". We will discuss it further in this article.

expression
A character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start
An integer or an expression that can be implicitly converted to int, which specifies where the substring begins.

length
An integer or an expression that can be implicitly converted to int, which specifies the length of the substring.

WHERE search_condition. Excuse me for not going in to much detail here but my primary issue today is to discuss the PATINDEX function.

Now that we are done with the formalities of the syntax and arguments we can do some programming. In the example below I will create a temporary table and populate it with all the months of the year and work from that.

create table #mymonth(
monthname varchar(50)
)
insert #mymonth(monthname)
select 'January'
union all select 'February'
union all select 'March'
union all select 'April'
union all select 'May'
union all select 'June'
union all select 'July'
union all select 'August'
union all select 'September'
union all select 'October'
union all select 'November'
union all select 'December'

Now I will use the PATINDEX function in the WHERE clause to only select the months with and "e" in the name. The PATINDEX function results in an integer value and for that reason we want to filter the results of this select where the PATINDEX is greater then zero. If we wanted to exclude the months with and "e" then we would want the PATINDEX to be equal to zero.

select monthname from #mymonth where PATINDEX('%e%',monthname) > 0

Result:

February
June
September
October
November
December

In the following piece of code I will be using the LEFT and SUBSTRING functions with PATINDEX to strip the first "e" from all months that has an "e" in them

select LEFT(monthname,PATINDEX('%e%',monthname)-1)+SUBSTRING(monthname,PATINDEX('%e%',monthname)+1,len(monthname)) as alteredmonth from #mymonth where PATINDEX('%e%',monthname) > 0

Result:

Fbruary
Jun
Sptember
Octobr
Novmber
Dcember

You will notice that in the LEFT function I used PATINDEX minus one because the PATINDEX gives you the exact position of the "e". There for if I had not minus it with one, the "e" would still have been included. Likewise in the SUBSTRING I used the PATINDEX with plus 1 to exclude the "e".

Let's make it a bit more interesting now. I will create another table called "#concatmonth" and populate it with January, February and March concatenated. I will then first of all strip January out of this concatenation. We will strip the others of a bit later on in the article.

create table #concatmonth(
monthname varchar(255)
)
insert #concatmonth(monthname)
select 'January:February:March' select LEFT(monthname,PATINDEX('%:%',monthname)-1) as alteredmonth from #concatmonth Result: January

I will need the LEFT and SUBSTRING functions together with the PATINDEX to strip all three months in three different columns. Just to show you how it progresses I will have a couple of columns showing the original, first month, second part with February and March and then eventually the last two months

select monthname as original, LEFT(monthname,PATINDEX('%:%',monthname)-1) as firstmonth, SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)) secondpart from #concatmonth

Result:

January:February:March | January | February:March |

Now we will add the last two columns where I will be use SUBSTRING inside LEFT and SUBSTRING.

select monthname, LEFT(monthname,PATINDEX('%:%',monthname)-1) as firstmonth, SUBSTRING(monthname,PATINDEX('%:%',monthname)1,
LEN(monthname)) secondpart,
LEFT(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)),
PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1, LEN(monthname)))-1) secondmonth,
SUBSTRING(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1, LEN(monthname)),
PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1, LEN( monthname)))+1,LEN(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)))) thirdmonth
from #concatmonth Result: January:February:March | January | February:March | February | March

Can you now see why I had such a hard time to learn this! You have to continuously make sure that your brackets is in place but with SQL Server 2005 it bolds the open bracket and close bracket when you type the close bracket so it is easy to get it right. There is hundreds of other ways to use these functions and the way I used here is most probably not the best way so, if anyone out there have any suggestions then I'm always open to new ideas. Do a WHILE loop to strip the months and insert them into one column with multiple rows. I also always forget the syntax of the functions in T-SQL but after this article I know them of by heart. So if you want to memorize functions that you can't remember the syntaxes for, write an article for SQLServerCentral.com. I hope this article will be and has been useful to at least one person out there.

Total article views: 9466 | Views in the last 30 days: 14
 
Related Articles
FORUM

patindex problem

patindex problem

FORUM

SubString

Assistance with SUBSTRING in SSIS Expression in Derived Column Transformation

FORUM

Substring with Charindex or Patindex and case statement

Using case statement and substring to clean up data

FORUM

Patindex

Comments posted to this topic are about the item [B]Patindex[/B] i have a job on my server which tak...

FORUM

patindex - search for either value

patindex - search for either value

Tags
string manipulation    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones