CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Yves
Yves

Reputation: 12497

How can I extract string 'foo' from a string [email protected]?

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.

Upvotes: 1

Views: 15781

Answers (5)

Zoomstrat
Zoomstrat

Reputation: 1

Here's a solution that works in a situation where you know what the domain part of the email address is going to be, but you want to extract the first part, which will be the username. For example, I work for a college where email addresses are [email protected], and firstname.lastname is used as the login username. Sometimes I need a list of just usernames, but those are not stored in our database separately from the entire email address. So, I use this: 

REPLACE(email_address,'@college.edu','')

Upvotes: 0

StampedeXV
StampedeXV

Reputation: 2805

select left(emailaddress, charindex('@', emailaddress)-1)

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881173

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.

Upvotes: 2

Grzegorz Oledzki
Grzegorz Oledzki

Reputation: 24251

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))

Upvotes: 0

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

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.

Upvotes: 16

Related Questions