SELECT LIKE search term

[Craig Hopson]

hi guys i am having trouble with the syntax of a query here is what i have not shore where to go from here

select * from `jcow_accounts` WHERE 1 AND (username LIKE craig% OR email LIKE craig% OR fullname LIKE craig%) AND birthyear>1953 AND birthyear<1995 AND gender=0 and !hide_me order by lastlogin DESC LIMIT 20
[/CODE]

Thanks

[M_Lyons10]

SQL? What's it doing wrong? Are you getting an unexpected result set? An error? It's not returning anything?

[neufuse Veteran]

your like clauses are strings and need encapsulated in single quotes also.... LIKE '%craig' LIKE '%craig%' LIKE 'Craig%' etc

also what SQL variant? MS SQL, Oracle and MySQL all have differences even though they are close to the SQL standard

on SQL Server I'd write that like this

SELECT TOP 20 *

FROM [JCOW_ACCOUNTS]

WHERE ([uSERNAME] LIKE 'craig%' OR LIKE 'craig%' OR [FullName] LIKE 'craig%')

AND [birthyear] between 1953 AND 1995

AND [Gender] = 0

and HIDE_ME <> 1

ORDER BY [LastLogin] DESC

[CentralDogma]

First,

1
[/CODE]

in the WHERE clause is not a propper boolean expression. What did you want to say with it?

[CODE]
1=1
[/CODE]

will give you ever row.

[CODE]
1<>1
[/CODE]

will give you none.

Also, you have to enclose your strings in single quotes:

[CODE]
username LIKE 'craig%' OR email LIKE 'craig%' OR fullname LIKE 'craig%'
[/CODE]

[Craig Hopson]

your like clauses are strings and need encapsulated in single quotes also.... LIKE '%craig' LIKE '%craig%' LIKE 'Craig%' etc

Worked like a charm thanks so much, couldn't see for looking lol

Thanks all