Get first occurance of a character in SQL

Get first occurance of a character in SQL

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

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:


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:


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!

One thought on “Get first occurance of a character in SQL

  1. Thanks, this was just what I was looking for…saved me a ton of time today on a last-minute report request from a Sales VP!!

Comments are closed.

Comments are closed.
%d bloggers like this: