SOLVED: SELECT WHERE REGEXP or Like

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)

SOLVED: SELECT WHERE REGEXP or Like

Author

Topic

eric1328
Starting Member

4 Posts

Posted - 2009-08-20 : 23:05:06

I want to do something like:

SELECT password FROM users WHERE username CONTAINS SOME OF A STRING

Suppose this is the users table:

USERNAME PASSWORD
test1 pass1
test2 pass2
test3 pass3

if the string in the query equals "test2@domain", it would return "pass2"...since the username contains a substring of the string

I've looked at using REGEXP and Like, but I'm confused.

Have any suggestions? Thanks!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-21 : 00:15:09

try contain or freetext
http://msdn.microsoft.com/en-us/library/ms187787.aspx

Hope can help...but advise to wait pros with confirmation...

gaussen
Starting Member

1 Post

Posted - 2009-08-21 : 02:52:17

SELECT password FROM users WHERE 'test2@domain' like '''%' +USERNAME+ '%'''

hmm,hello world...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-21 : 03:01:36

huh???correct me if im wrong...how the query works?

Hope can help...but advise to wait pros with confirmation...

eric1328
Starting Member

4 Posts

Posted - 2009-08-21 : 17:01:17

Yeah, that query didn't work for me, but thanks.

I couldn't get any CONTAINS or FREETEXT queries working. Does that work for MySQL?

Basicially i want to select passwords where username contains a substring (part of) of a variable string...its usally the other way around for most people

thanks for the help!

eric1328
Starting Member

4 Posts

Posted - 2009-08-21 : 22:15:46

Here's another way of explaining it:

SELECT password FROM users WHERE username = variable, but minus whatever is in front of the @ sign

for example, if variable is eric@abc, I want to cut eric out and only use abc in the query:

SELECT password FROM users WHERE username = ~~eric@~~abc

eric1328
Starting Member

4 Posts

Posted - 2009-08-21 : 22:15:47

I figured it out:

SELECT password FROM users WHERE username = SUBSTRING('VARIABLE',LOCATE('@', 'VARIABLE')+1);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources