CODEWITHSUNDEEP
sql
Kemp
Kemp

Reputation: 21

Select email address only (SQL)

i have column in a table (column name is “from”), looks like this

[email protected]
frank ocean real [email protected]
[email protected]
frits west [email protected]

I want to select the email addresses only, how do i do this? with a substring? I can find a domain, but i want to have the complete mail addresses, like this:

[email protected]
[email protected]
[email protected]
[email protected]

thanks!

Upvotes: 0

Views: 371

Answers (2)

AHiggins
AHiggins

Reputation: 7227

Reverse the string and look for a space before the address: try this

CREATE TABLE #Addresses (EmailAddress VARCHAR(100))
INSERT INTO #Addresses (EmailAddress)

SELECT '[email protected]'
UNION
SELECT 'frank ocean real [email protected]'
UNION
SELECT '[email protected]'
UNION
SELECT 'frits west [email protected]'

SELECT LTRIM(RTRIM(RIGHT(EmailAddress, CHARINDEX(' ', REVERSE(' ' + EmailAddress),CHARINDEX('@', REVERSE(' '+emailAddress)))))) FROM #Addresses

EDIT: if you have any strings that contain the name after the address, you can use the following to strip out the address:

CREATE TABLE #Addresses (EmailAddress VARCHAR(100))
INSERT INTO #Addresses (EmailAddress)

SELECT '[email protected]'
UNION
SELECT 'frank ocean real [email protected]'
UNION
SELECT '[email protected]'
UNION
SELECT 'frits west [email protected]'
UNION 
SELECT '[email protected] my name'

SELECT LTRIM(RTRIM(LEFT(RIGHT(EmailAddress, CHARINDEX(' ', REVERSE(' ' + EmailAddress),CHARINDEX('@', REVERSE(' '+emailAddress)))), CHARINDEX(' ', EmailAddress + ' '))) FROM #Addresses


DROP TABLE #Addresses

EDIT 2: forgot to add trimming functions

EDIT 3: final code using the OP's column name (table name not posted):

SELECT LTRIM(RTRIM(LEFT(RIGHT([From], CHARINDEX(' ', REVERSE(' ' + [From]),CHARINDEX('@', REVERSE(' '+[From])))), CHARINDEX(' ', [From]+ ' '))) FROM -- whatever your table is named

Upvotes: 1

XN16
XN16

Reputation: 5889

If the output is always in the format that you gave, then hopefully this will work:

SELECT RIGHT([From], CHARINDEX(' ', REVERSE(' ' + [From])) - 1) AS [Result]
FROM   YourTable

This will only work if there is a space before the input that you want (the actual email address). I use this for a similar purpose in for some legacy dodgy customer data.

This is for SQL Server, I don't know if it will work for any other RDBMS.

Upvotes: 0

Related Questions