Question Locked

SQL code question

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi Guys,

I have been tasked with writing a query to weed out unusable email addresses from our system. I am in training in SQL and was hoping you could help me work out the code for double or more '@' symbols in an address.

All the best

George

5 replies

by tony hopkinson

you want the Like function e.g.

Select * from Addresses Where Address Like '%@%@%'

% 0 to n characters
_ is a single character

also you can do
[abc]% which will find any string starting with a or b or c.

if you are using sql2005 and 2008 , you could write a .net function that uses regex, they are a bit slow, but you could beef it up considerable

Like '%@%.%' is simple start on good address formats.

by georgeyb

I am actually doing this in visual basic and not SQL server as I previously thought. D'oh any advice?
Thanks and sorry for the rookie mistake

by tony hopkinson

For a one off if you were familiar and comfortable with it, then it's a maybe.

As well as cleaining up the duff addresses, some one needs to stop putting duff ones going in.

In VB. VBA or VB6 then the Split function would be a start.

Split('@') should return and array of two strings, before and after the @
0 would be an empty address
1 would be a string starting or ending with @
2 is OK
More is WTF.

You could also split the second part on . to see if you have something@something.something

VB.net, I'd be using RegEx, but that has split as well

I always have to look that sort of thing up, try here, one you've done a few .Net regex tutorials.

http://www.regular-expressions.info/email.html

THis might come in handy as well

http://www.codeproject.com/KB/vb/regextester.aspx

by georgeyb

Perfect answer Tony, Thanks for your help especially 'more is WTF' haha It is a bit like that here !!

This discussion is closed to new replies.