Noob SQL question. SQL Patterns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Weasel5-12
Forum Commoner
Posts: 37
Joined: Tue Sep 16, 2008 6:58 am

Noob SQL question. SQL Patterns

Post by Weasel5-12 »

g'day,

i KNOW this will sound horrid to some, but i cant get it. please help.

I'm trying to query my DB, while looking for items that start with a particular character... say 3.

i've tried

Code: Select all

 
SELECT * FROM `movie_database`.`movie_list`WHERE `Title` LIKE '3'
SELECT * FROM `movie_database`.`movie_list`WHERE `Title` LIKE '^3'
SELECT * FROM `movie_database`.`movie_list`WHERE `Title` LIKE '^(3)'
 
[/color]

none of which work. Obviously.

Can some1 please help?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Noob SQL question. SQL Patterns

Post by Eran »

The LIKE statement is not a full regex engine, it has only two wildcard characters: '%' and '_'. http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
So for your requirements:

Code: Select all

 
SELECT * FROM `movie_database`.`movie_list` WHERE `Title` LIKE '3%'
 
Alternatively, you can use the REGEXP statement for full regexp capabilities - http://dev.mysql.com/doc/refman/5.0/en/regexp.html
User avatar
Weasel5-12
Forum Commoner
Posts: 37
Joined: Tue Sep 16, 2008 6:58 am

Re: Noob SQL question. SQL Patterns

Post by Weasel5-12 »

pytrin wrote:The LIKE statement is not a full regex engine, it has only two wildcard characters: '%' and '_'. http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
So for your requirements:

Code: Select all

 
SELECT * FROM `movie_database`.`movie_list` WHERE `Title` LIKE '3%'
 
Alternatively, you can use the REGEXP statement for full regexp capabilities - http://dev.mysql.com/doc/refman/5.0/en/regexp.html
Cheeers mate, i've tried running this query directly on my DB, and works perfectly, but no so when tryin 2 run it through my PHP scripts.
And now i get the error message;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC' at line 1

Code: Select all

 
$query [color=#0000FF]=[/color] [color=#FF0000]"SELECT * FROM movie_list WHERE Title LIKE '"[/color][color=#0000FF].[/color]$letter[color=#0000FF].[/color][color=#FF0000]"%' DESC"[/color];
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Noob SQL question. SQL Patterns

Post by Eran »

DESC can only follow an ORDER BY statement...
User avatar
Weasel5-12
Forum Commoner
Posts: 37
Joined: Tue Sep 16, 2008 6:58 am

Re: Noob SQL question. SQL Patterns

Post by Weasel5-12 »

Can u please give me an example of use of a REGEXP within an SQL statement?
Post Reply