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 4 Answers
CHARINDEX is what you are looking for
select CHARINDEX('@', '') ----------- 8 (1 row(s) affected) -or-
select CHARINDEX('c', 'abcde') ----------- 3 (1 row(s) affected) 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.
0One 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:
2I believe you want to use CHARINDEX. You can read about it here.
