[SOLVED] SELECT * FROM using >=

[denoteone]

I have a column named successPct  and the entries look like 95%, 85%, 99%  and so on.  Is it possible to write a select statement like what follows since there is a "%" in the number?

 

$query  = "SELECT * FROM my_table WHERE successPct  >= '55%'";

 

would this make sense?

[ldougherty]

That query actually does work, I've just tested it on my own database.

 

The interesting thing is that the column type I have is VARCHAR and it is equating values of integers properly.  I created two records, one with 55% and one as 65%

 

The query

SELECT * FROM my_table WHERE successPct  >= '60%'

 

Returned the 65% row..

 

One thing to note though is it also returned all text values because apparently anything alphabetical is greater than %

[akitchin]

1. have you tried to run that query? if so, you should be able to tell us whether or not it works.

 

2. in the case that it doesn't, you'll need to run the comparison against the integer portion of the column's value. trim the last character from the field's value, and convert to integers. functions for this can be found in the MySQL manual, OR:

 

3. do it right - simply store the success percentages as numbers. to add a percentage sign when outputting, simply append it manually at the time of echo.

 

EDIT: whoops, beaten to the punch. i still think #3 is the best solution though - change the column type to match the data it should be holding.

[denoteone]

The data was given to me as an .csv file. So i have no control over the values of the field.

 

But I am testing now. Thanks for the help.

[akitchin]

The data was given to me as an .csv file. So i have no control over the values of the field.

 

But I am testing now. Thanks for the help.

 

it's a quick query to go through and chop the percent symbol off of each field in the database, followed by converting the column type once you've done that.

[denoteone]

done thanks for the advice

 

on the same script is the best way to check to see if a variable is an integer by using regex?

[Maq]

Are you asking if regex is the best way to check for integers, or are you asking how to accomplish this in regex?

 

regex:

^\d+$

 

PHP:

is_int

 

 

[denoteone]

if it is the best way/only way.

 

[nitin]

ok dude just try the same query by editing or entering 9% in table. it will give u a wrong result  .

[Maq]

if it is the best way/only way.

 

 

It depends on how you are getting the value you should probably use the regex.

 

ok dude just try the same query by editing or entering 9% in table. it will give u a wrong result  .

 

I have no clue what you're talking about.

[denoteone]

it's a quick query to go through and chop the percent symbol off of each field in the database, followed by converting the column type once you've done that.

 

I thought that I knew how to use TRIM() but I am having issues. can someone help me with the SQL Statement to remove the "%" at the end of all entries in the accountPCT field?

[akitchin]

it's a quick query to go through and chop the percent symbol off of each field in the database, followed by converting the column type once you've done that.

 

I thought that I knew how to use TRIM() but I am having issues. can someone help me with the SQL Statement to remove the "%" at the end of all entries in the accountPCT field?

 

you'd be looking at RTRIM(), but that function only removes whitespace from the end of the string. i'd imagine the most effective route is to use REPLACE():

 

UPDATE table SET column = REPLACE(column, '%', '')

 

once that's done, you can change the column type and it should retain the correct numbers. perhaps backup the column values before change the column type, just in case.

[michael624]

NO NO NO!

STOP RIGHT THERE!

 

In a database if you are holding percents keep it as an integer or real. Not as a varchar, although php will convert it for you. BUT never store the percent sign, it goes against ALL database rules!

 

store the percents as numbers and add a % sign when you are outputting the text. Then your original query with >= will work.

 

Although if you cant or dont want to make an import script, you can trim and convert

I dont remember the exact syntax but this should be somewhat it:

toint(substr(successPct,0,2))

 

what it does is take the first two letter and convert them to numbers

letters:'12%' to int 12

[akitchin]

NO NO NO!

STOP RIGHT THERE!

 

In a database if you are holding percents keep it as an integer or real. Not as a varchar, although php will convert it for you. BUT never store the percent sign, it goes against ALL database rules!

 

store the percents as numbers and add a % sign when you are outputting the text. Then your original query with >= will work.

 

Although if you cant or dont want to make an import script, you can trim and convert

I dont remember the exact syntax but this should be somewhat it:

toint(substr(successPct,0,2))

 

what it does is take the first two letter and convert them to numbers

letters:'12%' to int 12

 

i suppose reading the thread before replying is overrated.

[MadTechie]

what it does is take the first two letter and convert them to numbers

letters:'12%' to int 12

or even 110% into 11 

 

Oh and my 2pence

UPDATE table SET column = CAST(column AS SIGNED)