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.