help with query (regex?) - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with query (regex?)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-29 : 04:13:29

Hi,

I am attempting to write a query to find patterns of some spammer activity. (they are getting by captcha currently)

USERNAME: WORDHERE
EMAIL ADDRESS: WORDHERE_99TT@emailprovider.com

Basically the pattern is, the username is the characters before the first underscore. The underscore is then followed by 2 characters which are both 0-9. It is then followed by two a-z characters.

I am trying to do something like

SELECT * from TblUsers WHERE (meet emailaddress/username conditions)

any help is greatly appreciated, as always!

Thanks once again
mike123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 04:20:14

Try

SELECT * from TblUsers WHERE emailaddress like username+'_[0-9][0-9][a-zA-Z][a-zA-Z]%'

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-29 : 04:30:08

Hi madhivanan,

I thought this would work, but its bringing back a bunch of false positives..

Some things to keep in mind.

All these stipulations occur BEFORE the "@" in the email address. I tried adding a "@" into the query, but still some false positives.

For example, this is brought back, which it shouldnt be.

herman
herman222us@domain.com

The query doesnt seem to be requiring an "_" currently ?

thanks again!
mike123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 05:07:57

Try

SELECT * from TblUsers WHERE emailaddress like username+'[_][0-9][0-9][a-zA-Z][a-zA-Z]%'

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-29 : 18:54:38

hi madhivanan,

getting closer but still some results are not filtered

for example, this is brought back .. we need to limit only 2 characters after the integer values...

any suggestions?

thanks once again!

lookinatit
lookinatit_88male@serviceprovider.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 02:17:13

Try

SELECT * from TblUsers WHERE emailaddress like username+'[_][0-9][0-9][a-zA-Z][a-zA-Z]@%'

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 03:17:16

SELECT * from TblUsers WHERE emailaddress like username+'[_][a-zA-Z][a-zA-Z][0-9][0-9][@][a-z0-9]%[.][a-z]%'

i think this works.

Iam a slow walker but i never walk back

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-30 : 05:28:50

Awesome, this is working great..

just 1 step further if you are able to help me ..

currently its working great for these situtations

USERNAME: WORDHERE
EMAIL ADDRESS: WORDHERE_99TT@emailprovider.com

but what if it was like this

USERNAME: WORDHERE99TT
EMAIL ADDRESS: WORDHERE_99TT@emailprovider.com

basically where the username contains whats after the "_" and the underscore is removed....

any help again greatly appreciated and very very helpful!

thanks again,
mike123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 05:34:18

quote:
Originally posted by mike123

Awesome, this is working great..

just 1 step further if you are able to help me ..

currently its working great for these situtations

USERNAME: WORDHERE
EMAIL ADDRESS: WORDHERE_99TT@emailprovider.com

but what if it was like this

USERNAME: WORDHERE99TT
EMAIL ADDRESS: WORDHERE_99TT@emailprovider.com

basically where the username contains whats after the "_" and the underscore is removed....

any help again greatly appreciated and very very helpful!

thanks again,
mike123

So is that still invalid and you want to get that too?

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-30 : 05:41:54

Hey Madhivanan,

Actually just looking for 2 seperate queries.

This is the format I am looking to bring back :

USERNAME: WORDHERE99TT
EMAIL ADDRESS: WORDHERE_99TT@emailprovider.com

thanks very much!! :)
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources