LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 04-26-2005, 11:36 PM   #1
hus
Member
 
Registered: Dec 2003
Location: Bangkok- Thailand
Distribution: Mandriva 10.0,Fedora Core 4
Posts: 79

Rep: Reputation: 15
compare string in SQL


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.
 
Old 04-27-2005, 12:54 AM   #2
mrGenixus
Member
 
Registered: Dec 2004
Location: Colorado, US
Distribution: gentoo, debian, ubuntu live gnome 2.10
Posts: 440

Rep: Reputation: 30
You are aware, of course, that tis is a linux forum? have you tried the activewin forums for this question
 
Old 04-27-2005, 08:30 AM   #3
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
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.

Last edited by deiussum; 04-27-2005 at 08:32 AM.
 
Old 04-27-2005, 08:35 AM   #4
mrGenixus
Member
 
Registered: Dec 2004
Location: Colorado, US
Distribution: gentoo, debian, ubuntu live gnome 2.10
Posts: 440

Rep: Reputation: 30
my appologies
 
Old 04-27-2005, 09:14 AM   #5
michaelsanford
Member
 
Registered: Feb 2005
Location: Ottawa/Montréal
Distribution: Slackware + Darwin (MacOS X)
Posts: 468

Rep: Reputation: 30
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)

Last edited by michaelsanford; 04-27-2005 at 09:16 AM.
 
Old 04-27-2005, 09:16 AM   #6
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
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.

Last edited by deiussum; 04-27-2005 at 09:23 AM.
 
Old 04-27-2005, 09:19 AM   #7
michaelsanford
Member
 
Registered: Feb 2005
Location: Ottawa/Montréal
Distribution: Slackware + Darwin (MacOS X)
Posts: 468

Rep: Reputation: 30
(sorry, I edited and hedged my post on that while you were replying )
 
Old 04-27-2005, 09:24 AM   #8
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
Quote:
Originally posted by michaelsanford
(sorry, I edited and hedged my post on that while you were replying )
heheh, np.
 
Old 04-27-2005, 10:06 AM   #9
deiussum
Member
 
Registered: Aug 2003
Location: Santa Clara, CA
Distribution: Slackware
Posts: 895

Rep: Reputation: 32
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.

Last edited by deiussum; 04-27-2005 at 10:08 AM.
 
Old 04-27-2005, 10:17 AM   #10
michaelsanford
Member
 
Registered: Feb 2005
Location: Ottawa/Montréal
Distribution: Slackware + Darwin (MacOS X)
Posts: 468

Rep: Reputation: 30
Interesting! Thanks for posting that.

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


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 09:45 AM
Migrating from MS-SQL server to My-SQL emailssent Linux - Networking 2 02-07-2005 02:20 PM
Which SQL is suitable for EMbedded SQL on C? hk_michael Programming 4 01-10-2005 05:07 PM
java test if string in string array is null. exodist Programming 3 02-21-2004 01:39 PM
compare string in C++ danxl Programming 3 11-02-2003 02:14 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 04:30 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration