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
today (Sept 2009) in SQL for a substring portion of a query I was creating, those obviously
aren’t available. Well, in Oracle InStr is available I understand, 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!