SQL code question

By GeorgeyB ·
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


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Aside from a lot of messing about

by Tony Hopkinson In reply to SQL code question

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.

Collapse -

Excuse my newbness

by GeorgeyB In reply to Aside from a lot of messi ...

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

Collapse -

Well yes don't

by Tony Hopkinson In reply to Excuse my newbness

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, 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.

THis might come in handy as well

Collapse -

Nice one

by GeorgeyB In reply to Well yes don't

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

Related Discussions

Related Forums