Single Quote (') and Double Quote (")

[c***@hotmail.com]

Can I use double quote (") in the SELECT Query?

Select *
From ATABLE
Where ACOLUMN = "xyz's"

If Yes, How to set it?
Thanks in advance..

[Mark A]

Post by c***@hotmail.com
Can I use double quote (") in the SELECT Query?
Select *
From ATABLE
Where ACOLUMN = "xyz's"
If Yes, How to set it?
Thanks in advance..

Use this instead:

Select *
From ATABLE
Where ACOLUMN = 'xyz''s'

the above contains 2 single quotes between z and s.

[c***@hotmail.com]

Thanks Mark!
Yeap! this is a solution too!

But ...
Problem is my data.
The data is from my old database,
and some more it is not DB2,
and unfortunately the data consist of single quote (').

That's why I ask
can I use double quote instead of single quote in the select query.

On the DB2 document,
I read something about the SQL_STRING_DELIMITED setting.
But I have no idea how to set this value.
Anyone, any idea?

Thanks again.

[Knut Stolze]

Post by c***@hotmail.com
Can I use double quote (") in the SELECT Query?
Select *
From ATABLE
Where ACOLUMN = "xyz's"
If Yes, How to set it?

SQL uses single-quotes to mark a string. You can't use double-quotes for
that. (Same as in other programming languages, i.e. C/C++ uses " for
strings and ' for characters.) Double-quotes in SQL are used to indicated
delimited identifiers (schema/table/column/... names). Delimited
identifiers can have mixed case and special characters.

Note that some other database systems (not DB2) treat this differently than
the SQL standard mandates. You shouldn't do that because it will just
invite problems down the road.

[c***@hotmail.com]

Thanks Knut.

I know it is not a good idea to use double quote (") in the SQL.
But our database (Not DB2) data consist single quote (').

We are planning to migrate from our origin database to DB2.
Any suggestion to do with this issue?

[Knut Stolze]

Post by c***@hotmail.com
Thanks Knut.
I know it is not a good idea to use double quote (") in the SQL.
But our database (Not DB2) data consist single quote (').
We are planning to migrate from our origin database to DB2.
Any suggestion to do with this issue?

How do you do the migration? If you have an application that extracts the
data from the old system and inserts into DB2, you can use parameter
markers/host variables and then just ignore the content of the data -
quotes inside the strings will be handled correctly. The same applies to
migration with DB2's federated features.

If you migrate via external, text-based files, you will have to worry about
such characters, of course.

[c***@hotmail.com]

Finally, I understand why the user sometime need to use single quote but not double quote...

It is coz the field "Subject", which use to describe the "description" field.
e.g. It's .....
And there is number of SQL search based on this subject field.

In my old database, I allow to use double code. e.g.
Select * from CommentTBL where Subject like "It%"
So there is no issue about the single quote or double quote.

And It is impossible to change the data double quote to single quite.
coz we can write something like this...
It"s ...
Just does not sound right, isn't it?

[Knut Stolze]

Post by c***@hotmail.com
Finally, I understand why the user sometime need to use single quote but
not double quote...
It is coz the field "Subject", which use to describe the "description"
field. e.g. It's .....
And there is number of SQL search based on this subject field.
In my old database, I allow to use double code. e.g.
Select * from CommentTBL where Subject like "It%"

This is not valid SQL, so you should never rely on it. It won't work in
most DBMS.

Post by c***@hotmail.com
And It is impossible to change the data double quote to single quite.
coz we can write something like this...
It"s ...
Just does not sound right, isn't it?

If you have two single quotes in the text, you simply escape both of them.
Escaping single-quotes is done by doubling them. That's the correct
approach.

So if you have It's in the text, you write:

... LIKE 'It''s%'

[c***@hotmail.com]

Thanks Knut.

I manage to convert all double quote to single quote.
Good news is the test so far did not give any problem.
Thanks again for your help.