SELECT WHERE REGEXP or Like Query

eric1328

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!

johanafm

LIKE 'test2&#37;'

eric1328

that didn't work, but thanks for the help

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

johanafm

Ah, now I get what you mean

SELECT username, password FROM users WHERE INSTR('yourlongerthannamestring', username) = 1;

eric1328

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

I figured it out:

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