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

Solution 1

CHARINDEX is what you are looking for

select CHARINDEX('@', '[email protected]')
-----------
8

(1 row(s) affected)

-or-

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

(1 row(s) affected)

Solution 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.

Solution 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:

http://en.wikipedia.org/wiki/Email_address

Solution 4

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

View more solutions

Comments

[DevelopingChris]

these are the kinds of things that I'm trying to determine and fix in our database. Mainly people just mis type their domain name. most web redirect back to the real one but the mx records don't forward, and displaying them gets awkward

[Alex]

There's a special place in heck for people who put @'s in the first part of their email addresses ... like people who put spaces in table/field names ...