Jump to content



Photo

SELECT LIKE search term


  • Please log in to reply
4 replies to this topic

#1 Craig Hopson

Craig Hopson

    Neowinian

  • Joined: 17-November 12
  • Location: London
  • OS: Windows 8 :-(

Posted 20 May 2013 - 20:12

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

Thanks


#2 M_Lyons10

M_Lyons10

    Neowinian Senior

  • Joined: 21-October 08
  • Location: Pennsylvania

Posted 20 May 2013 - 20:19

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

#3 vetneufuse

neufuse

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 16-February 04

Posted 20 May 2013 - 20:23

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 [EMAIL] LIKE 'craig%' OR [FullName] LIKE 'craig%')
AND [Birthyear] between 1953 AND 1995
AND [Gender] = 0
and HIDE_ME <> 1
ORDER BY [LastLogin] DESC


#4 CentralDogma

CentralDogma

    Neowinian Senior

  • Joined: 29-February 08

Posted 20 May 2013 - 20:24

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

1=1
will give you ever row.

1<>1
will give you none.


Also, you have to enclose your strings in single quotes:
username LIKE 'craig%' OR email LIKE 'craig%' OR fullname LIKE 'craig%'


#5 OP Craig Hopson

Craig Hopson

    Neowinian

  • Joined: 17-November 12
  • Location: London
  • OS: Windows 8 :-(

Posted 20 May 2013 - 20:27

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