Indexof Function in T-Sql

Question

Given an email address column, I need to find the position of the @ sign for substringing.

What is the indexof function, for strings in T-SQL?

Looking for something that returns the position of a substring within a string.

in C#

var s = "abcde";
s.IndexOf('c'); // yields 2

Accepted Answer

CHARINDEX is what you are looking for

select CHARINDEX('@', '')
-----------
8

(1 row(s) affected)

-or-

select CHARINDEX('c', 'abcde')
-----------
3

(1 row(s) affected)

Answer 2

You can use either CHARINDEX or PATINDEX to return the starting position of the specified expression in a character string.

CHARINDEX('bar', 'foobar') == 4
PATINDEX('%bar%', 'foobar') == 4

Mind that you need to use the wildcards in PATINDEX on either side.

Answer 3

One very small nit to pick:

The RFC for email addresses allows the first part to include an "@" sign if it is quoted. Example:

"john@work"@myemployer.com

This is quite uncommon, but could happen. Theoretically, you should split on the last "@" symbol, not the first:

SELECT LEN(EmailField) - CHARINDEX('@', REVERSE(EmailField)) + 1

More information:

Answer 4

I believe you want to use CHARINDEX. You can read about it here.

4 Answers