select left(emailaddress, len(emailaddress) - charindex('@', emailaddress))
I am getting the result below:
foo@ma
Please correct the select statement below. I need to do this with tones of email addresses and extract the username ONLY.
views:
137answers:
4
+9
A:
You're inadvertently re-implementing "right" there :)
Try this:
select left(emailaddress, charindex('@', emailaddress) - 1)
It seeks the position of the @, and takes the number of characters up to but not including (that's the - 1) the @ symbol.
Be careful with strings that don't have an @ though; you'll end up an empty string.
Jeremy Smyth
2009-09-17 14:16:42
I don't know, why -1? charindex gives an index, and left takes a length as far as I understood that?
StampedeXV
2009-09-17 14:20:08
Ayup. Charindex counts from the left, so if you want the left part of the string, just subtract 1 so you don't include the search character (@) itself. You'd only need the length of the string if you wanted the *right* part of the string, i.e. *after* the search character.
Martha
2009-09-17 14:21:05
example: [email protected] returns 1left( [email protected], 1) returns a -> what we want. so we don't need the minus 1.
StampedeXV
2009-09-17 14:24:20
@StampeedXV, for [email protected], charindex returns 2, not 1.
Gordon Bell
2009-09-17 14:28:21
@Jeremy thanks a bunch - very helpful specially the strings that do not have an '@' - thanks for the tip :)
Kombucha
2009-09-17 14:29:11
Damn. Made an error in my test :(. Although returning 2 is not what I expected in the first place.
StampedeXV
2009-09-17 14:32:22
A:
Just a guess. I don't have any access to sql-server, not a familiarity with it. But...
Have you tried:
select left(emailaddress, charindex('@', emailaddress))
Grzegorz Oledzki
2009-09-17 14:17:06
This would include the @ character. You need to subtract 1 to get all the characters up to but not including the search character.
Martha
2009-09-17 14:23:00
Actually, that depends if strings are zero-indexed or 1-indexed. If zero-indexed, charindex returns 3, thus you get the 3 left characters. A 1-based string would return 4, thus it would include the '@'.
Workshop Alex
2009-09-17 14:29:35
But in TransactSQL, charindex is 1-based! See http://msdn.microsoft.com/en-us/library/ms186323.aspx
Workshop Alex
2009-09-17 14:32:04
You are absolutely right. I was just guessing based on the output given by OP.
Grzegorz Oledzki
2009-09-17 15:02:57
Heh. Now the question got changed (e-mail change). Let's assume the editor provided the correct output for the original query (the one with len - charindex).
Grzegorz Oledzki
2009-09-17 15:04:40
+1
A:
If you need to do it with tons (or tonnes but certainly not tones) of email addresses and/or you're doing it frequently, the right time to do it is when the data enters the table (by using an insert/update trigger) and splitting it into two columns at that point. That way, it only happens when needed, not every time you do a select on the table.
Data is almost always read far more often than written so, by splitting at insertion/update time, you minimize the amount of work to be done. Per-row functions never scale well as your tables get bigger and it's a much smaller cost to simply concatenate two columns than to split them based on a character separator.
The other answers already tell you how to do the split. It's my contention that you're doing it at the wrong time. Of course, if a schema change is out of the question, just ignore this response. But, in my opinion, that would be the best way to go.
paxdiablo
2009-09-17 14:18:30