views:

1897

answers:

4

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
+9  A: 

CHARINDEX is what you are looking for

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


-----------
8

(1 row(s) affected)
Scott Ivey
+3  A: 

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

Justin Swartsel
+4  A: 

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.

OMG Ponies
+1 for reminding me that this is a 1 based system
jp2code
+1  A: 

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%5Faddress

richardtallent
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
DevelopingChris