MySQL

Critical problem "Like"

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2009

Posts: 11

Reputation: saurav.prasad28 is an unknown quantity at this point

Solved Threads: 0

saurav.prasad28 saurav.prasad28 is offline

Offline

Newbie Poster

Critical problem "Like"

May 11th, 2009

hi all Database Masters

Here i have suffer one critical issue,i have two column a and b,

Table name : Student
"A" "B"
"1" "SAntOsh"
"2" "SAuraV"
"3" "SAriya"
"4" "SAnju"
"5" "SAthiYa"

These are the sample records from my Table field. The "B" column always start with 'SA', the problem now in column B that has capital letter and I need to sort out all B that only has CAPITAL letter inside the letter and the out put will be displayed like this

A B
"1" "SAntOsh"
"2" "SAuraV"
"5" "SAthiYa"

Please help me out for this question

Thanks in Advance

•

Join Date: Aug 2007

Posts: 165

Reputation: Fest3er is an unknown quantity at this point

Solved Threads: 18

Fest3er

Offline

Junior Poster

Re: Critical problem "Like"

May 11th, 2009

•

Originally Posted by saurav.prasad28

... I need to sort out all B that only has CAPITAL letter inside the letter and the out put will be displayed like this

A B
"1" "SAntOsh"
"2" "SAuraV"
"5" "SAthiYa"

Please help me out for this question

Thanks in Advance

You need a regular expression. Something like this will match rows that contain at least one capital letter in the remainder of the field:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT * FROM myTable WHERE myField REGEXP 'CAPS.*[A-z]';
select * from myTable where myField regexp 'CAPS.*[A-z]';

•

Join Date: Apr 2009

Posts: 11

Reputation: saurav.prasad28 is an unknown quantity at this point

Solved Threads: 0

saurav.prasad28 saurav.prasad28 is offline

Offline

Newbie Poster

Re: Critical problem "Like"

May 11th, 2009

•

Originally Posted by Fest3er

You need a regular expression. Something like this will match rows that contain at least one capital letter in the remainder of the field:
Help with Code Tags

MySQL Syntax (Toggle Plain Text)
SELECT * FROM myTable WHERE myField REGEXP 'CAPS.*[A-z]';
select * from myTable where myField regexp 'CAPS.*[A-z]';

hi Fest3er

Thanks for giving the reply

but when I try which querry you send me but it doesn't give the out put

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM student WHERE b REGEXP 'CAPS.*[A-z]';
select * from student where b regexp 'CAPS.*[A-z]';

Please help me out.

thanks

Last edited by peter_budo; May 11th, 2009 at 7:01 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Aug 2007

Posts: 165

Reputation: Fest3er is an unknown quantity at this point

Solved Threads: 18

Fest3er

Offline

Junior Poster

Re: Critical problem "Like"

May 11th, 2009

•

Originally Posted by saurav.prasad28

hi Fest3er

Thanks for giving the reply

but when I try which querry you send me but it doesn't give the out put

select * from student where b regexp 'CAPS.*[A-z]';

Please help me out.

thanks

You're quite right; it doesn't work, and wasn't supposed to work verbatim.

The regular expression needs to be a pattern that matches your data, not the fictitious data in my fictitious table. That bit of learning is left as an exercise for the student (you).