compare string in SQL

hus · 04-26-2005, 11:36 PM

In MS-Access when I want to query like this

select client from thameswater where client="10.66.15.18"
(Not work)

It's not work Why but when I compare attribute with integer
it's Ok

select client from thameswater where id=52
(Work Fine)

Why I can't use = (equal) to compare between 2 string
The query result is empty Why?

Thanks in advance.

mrGenixus · 04-27-2005, 12:54 AM

You are aware, of course, that tis is a linux forum? have you tried the activewin forums for this question

deiussum · 04-27-2005, 08:30 AM

Put your string in single quotes, not double quotes. Double quotes in SQL usually indicate an identifier like a field or table name.

BTW, this is a Programming form and non-Linux programming is allowed...

The category the form is under is Non-*NIX Forums, and the description is:

Quote:

This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

mrGenixus · 04-27-2005, 08:35 AM

my appologies

michaelsanford · 04-27-2005, 09:14 AM

Double quotes should be fine, it's backticks that try to relate table names (at least in true SQL). Of course if this were a PHP script and the double quotes weren't escaped...

What version of MySQL are you running? Also, are you certain that there exists a client="10.66.15.18" at all?

I can execute a similar query with strings and " " with no problems (in MySQL 4.0.23a). Access may treat SQL differently but as far as I know MySQL sticks pretty close to the SQL standard, MSA may um, not.

Code:

mysql> select * from distinctive_vowels;
+----------+------+-----+-----+------+-------+-------+
| symbol   | high | low | ATR | back | front | round |
+----------+------+-----+-----+------+-------+-------+
| & #x0069; | +    | -   | +   | -    | +     | -     |
| & #x026A; | +    | -   | -   | -    | +     | -     |
| & #x0065; | -    | -   | +   | -    | +     | -     |
| & #x025B; | -    | -   | -   | -    | +     | -     |
| & #x0061; | -    | +   | +   | -    | +     | -     |
| & #x0252; | -    | +   | -   | +    | -     | -     |
| & #x0254; | -    | -   | -   | +    | -     | +     |
| & #x006F; | -    | -   | +   | +    | -     | +     |
| & #x028A; | +    | -   | -   | +    | -     | +     |
| & #x0075; | +    | -   | +   | +    | -     | +     |
| & #x026F; | +    | -   | +   | +    | -     | -     |
| & #x0079; | +    | -   | +   | -    | +     | +     |
| Ã¸          | -    | -   | -   | -    | +     | +     |
| & #x0153; | -    | -   | -   | -    | +     | +     |
+----------+------+-----+-----+------+-------+-------+
14 rows in set (0.00 sec)

mysql> select * from distinctive_vowels where symbol="& #x0069;";
+----------+------+-----+-----+------+-------+-------+
| symbol   | high | low | ATR | back | front | round |
+----------+------+-----+-----+------+-------+-------+
| & #x0069; | +    | -   | +   | -    | +     | -     |
+----------+------+-----+-----+------+-------+-------+
1 row in set (0.00 sec)

(Sorry the key of that table is an XHTML character entity so I had to fudge the spaces)

deiussum · 04-27-2005, 09:16 AM

His first post mentions Access, not MySQL. In MS-SQL Server and Access double quotes ARE used to denote identifiers like table/field names. The same is true for IBM's DB2. And it's been awhile since I've used it, but I think the same applies to Oracle.

Even though MySQL supports using the double quotes, single quotes would probably be better to use as it is more ANSI compliant SQL.

michaelsanford · 04-27-2005, 09:19 AM

(sorry, I edited and hedged my post on that while you were replying

)

deiussum · 04-27-2005, 09:24 AM

Quote:

Originally posted by michaelsanford
(sorry, I edited and hedged my post on that while you were replying )

heheh, np.

deiussum · 04-27-2005, 10:06 AM

Just out of curiosity, I checked out the SQL-92 BNF a bit. I've always used single quote identifiers in MySQL as well, just because I knew that was the ANSI standard. It surprised me that MySQL also allowed you to use double quotes for string literals.

Here's some bits of the BNF from here

Quote:

<character_string_literal> ::=
[ <introducer> ]
<quote> [ <character_representation>... ] <quote>
[ { <separator>... <quote> [ <character_representation>... ] <quote> }... ]

<quote> ::= '

Quote:

<column_name> ::= <identifier>

<identifier> ::=
[ <introducer> ] <actual_identifier>

<actual_identifier> ::=
<regular_identifier>
| <delimited_identifier>

<delimited_identifier> ::=
<double_quote> <delimited_identifier_body> <double_quote>

<double_quote> ::= "

It would appear that the double quote usage is one place that MySQL is not compliant with the SQL-92 standard... I was thinking that maybe the standard allowed the implementation to define the indentifier delimiter, but apparently it states that a double quote is to be used.

As a side note, in Access and MS-SQL server, the left/right brackets can be used in place of double quotes as well.

michaelsanford · 04-27-2005, 10:17 AM

Interesting! Thanks for posting that.

As a side note I always use single quotes anyway, so I guess that's the way to go...