find something after an @ - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

find something after an @

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2008-11-12 : 06:41:39

I need to find duplicate domain names in my emailaddress field.

Not sure how to go about it but for example if an addresses appeared like this:

info@johnny.co.uk
sales@johnny.co.uk

I'd want it return those in my qry. Hope it makes sense

JT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 07:02:22

SELECT Col1, SUBSTRING(Col1, CHARINDEX('@', Col1) + 1, LEN(Col1))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-11-12 : 07:08:00

that appears to just bring up everything in my database but without the @ or the bit before the @

JT

Topaz
Posting Yak Master

199 Posts

Posted - 2008-11-12 : 07:21:28

I should be changing col1 to my email address field right?

JT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 07:32:37

[code]
SELECT t.* FROM YourTable t
JOIN (SELECT SUBSTRING(Email,CHARINDEX('@',Email)+1,LEN(Email)) AS Domain
FROM YourTable
GROUP BY SUBSTRING(Email,CHARINDEX('@',Email)+1,LEN(Email))
HAVING COUNT(*) >1) tmp
ON t.Email LIKE % + tmp.Domain
[/code]

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-12 : 07:43:05

CREATE TABLE #tbl_emailid (id INT IDENTITY (1,1) CONSTRAINT pk_emaildif PRIMARY KEY , emailid VARCHAR(128))

CREATE TABLE #tbl_id (id INT, email VARCHAR(128))

INSERT INTO #tbl_emailid SELECT 'production@yahoo.co.uk'
INSERT INTO #tbl_emailid SELECT 'test@yahoo.co.uk'
INSERT INTO #tbl_emailid SELECT 'test@pbsys.co.uk'
INSERT INTO #tbl_emailid SELECT 'production@pbsys.co.uk'
INSERT INTO #tbl_emailid SELECT 'deve@hotmail.co.uk'

INSERT INTO #tbl_id
SELECT e.id, SUBSTRING(e.emailid,CHARINDEX('@',ei.emailid,1) + 1 ,LEN(e.emailid))
FROM #tbl_emailid AS e
INNER JOIN
#tbl_emailid AS ei ON e.id = ei.id

SELECT email,COUNT(email) FROM #tbl_id GROUP BY email HAVING COUNT(email) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources