(Originally posted September 2, 2009)
When I need to know the first (or any) occurrence of a character, I’m used to saying something like InStr() for VB or ASP, or String.IndexOf() in .Net, but when I tried to do something like this in SQL for a substring portion of a query I was creating, those obviously aren’t available. In Oracle InStr is available, but we`re talking about SQL.

To get the first occurrence of a character in a TEXT field (emphasis mine, because it must be a text compliant field) you use the PATINDEX() function. PATINDEX looks for the first occurrence of a specified pattern in a text field. Meaning, the field can be a text, nvarchar, char, etc. It cannot be an image or int, for example. PATINDEX, I learned, is typically used in BLOBs (Binary Large OBjects).  Let’s say you are querying a table called MYTABLE, looking for the pattern “forest” in the field STORY_DESCRIPTION. Your query would look like this:

SELECT PATINDEX(‘%,forest%’, STORY_DESCRIPTION)
FROM MYTABLE

The result would be the char position of the first occurrence of the pattern “forest”. This helped me when I was trying to put together something like this:

SELECT SUBSTRING(CN, 3, PATINDEX(‘%CN=’, CN) – 3)
FROM AD_TABLE

Where in this case I was querying an Active Directory results table and the CN field always started with “CN=”, but I wanted just the value from each row. This worked perfectly.
Although those of you who are SQL experts know of a much simpler method, I’m sure, this one worked for me!