Can you select a record with a dash / minus sign?

[jake66]

For the life of me, I cannot figure this out.

I have a script that displays the scores and sometimes they are negative. I can display every other sort of record I want, except when I try to pull all of the records with a - in it.

Am I approaching this correctly?

$test_query = ("select players_id from players_total where players_id > 5288 and text = '<b>0.00</b>' or '<b>-%'");

[phranque]

i think you have three problems here.
- if you were planning to use the '%' as a simple regular expression pattern matching character, it must be used with the 'LIKE' string comparison operator.
- which also points out that you aren't using the 'OR' correctly so the expression after the 'OR' is probably always true.
- also, i believe if you put your quoted expression in parentheses you just made it an array, so $test_query now contains an array reference instead of a string.

try this:

$test_query = "select players_id from players_total where players_id > 5288 and (text = '<b>0.00</b>' or text like '<b>-%'";

[perl_diver]

also, i believe if you put your quoted expression in parentheses you just made it an array, so $test_query now contains an array reference instead of a string.

You would think so but that is not the case. Perl does allow for some rather sloppy coding syntax at times. But the parenthesis should be removed, using them improperly like this is a bad habit that will eventually become a problem if continued.

His problem is not perl related anyway, it's an SQL question really. Perl is only the messenger in this case.

[rocknbil]

Well jake66, since the question is if you're approaching it correctly, you should really set the field called "text" as a decimal number. This would have the distinct advantage of making your select queries easier down the line. Consider the following:

$test_query = qq/select players_id from players_total where players_id > 5288 and numeric_field = '0.00' or numeric_field = '-';/;

If you set the default of "numeric_field" to 0.00, you'll never need '-':

alter table players modify numeric_field decimal(11,2) default '0.00'

So your query becomes

$test_query = qq/select players_id from players_total where players_id > 5288 and numeric_field = '0.00';/;

Secondly, consider the following queries you can now perform:

$test_query = qq/select players_id from players_total where players_id > 5288 and numeric_field > 0/;

$low = 10.00;
$high=20.00;

$test_query = qq/select players_id from players_total where players_id > 5288 and numeric_field = > '$low' and numeric_field < '$high'/;

$test_query = qq/select players_id from players_total where players_id > 5288 and and numeric_field > 0 order by numeric_field desc/;

The bold tags should be added in your script output, that way you can alter that externally at any time instead of changing your database:

<td><b>123.00</b></td>

to

<style type="text/css">
td .num { text-align:center; font-weight:700; color: #ff0000; }
</style>
.....

<td class="num">123.00</td>