Unable to query a a table with a value having special character 'single quote' in where clause 1

[VijiKumara]

Hello,

I have a table in Oracle called 'OPSD_TX_TEST_RESULTS' and it has a column called 'INSULATION'. The column Insulation has some of its values as CH' and CL'. I want to retrieve data from all the columns from this table for values CH' and CL' from Insulation field along with CH and CL. So this is what i am trying,

SELECT * FROM OPSD_TX_TEST_RESULTS WHERE INSULATION IN ('(CH')', '(CL')', 'CH', 'CL') --NOT WORKING
SELECT * FROM OPSD_TX_TEST_RESULTS WHERE INSULATION IN ('CH'', 'CL'', 'CH', 'CL') -- NOT WORKING
SELECT * FROM OPSD_TX_TEST_RESULTS WHERE INSULATION IN ('[CH']', '[CL']', 'CH', 'CL') -- NOT WORKING

I always get below error, which I don't understand.

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 40 Column: 67

So, can somebody throw some light on how to use the escape characters functionality here?

Your help is much Appreciated.

Regards,
Vijay

 

[dhookom]

Try this:

SELECT * FROM OPSD_TX_TEST_RESULTS WHERE INSULATION IN ('(CH'')', '(CL'')', 'CH', 'CL') --NOT WORKING

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Olaf Doschke]

Duane has the answer.

Just to show where you find that information: Here's the quote from the offical docs, how you escape quotes in string literals:

https://docs.microsoft.com/en-US/sql/t-sql/data-types/constants-transact-sql?view=sql-server-2017

If a character string enclosed in single quotation marks contains an embedded quotation mark, represent the embedded single quotation mark with two single quotation marks.

Just like the 'O''Brien' example there. That's two single quotation marks, not one double quotation mark.

As a single quote is the end of a string, it won't help you to use brackets instead, anything. When the single quote appears parsing code is - let's say - in string mode. Every character is part of the string until the next single quote. After that parsing goes back into code mode, and within a list, the next expected character is either a comma and the next string or the closing bracket ending the list.

So you can't have a single quote inside a string without some rule for it. And that's likely shown in the topic of types and constants or literals of types. Other languages most often use the backslash as escape character and also allow a combination of backslash and some letters to mean nonprintable or control characters, that would ruin code syntax when literally added to the string literal, especially carriage return or line feed, but also in that case you typically have the need to have that escape character in strings, too, so the double backslash is used to have a single backslash. Microsoft has decided to not introduce such escape mechanism, or let's put it differently, they have decided to not go with that norm and actually allow you to have a tab or carriage return within a string:

PRINT 'This
is
multiline
text'

This works and you get what you see, so this is WYSIWYG. The only character still needing such a special escape sequence is the string delimiter and therefore they chose this solution, the single quote becomes the escape character and the only escape sequence is doubling it. If it stays single, it's the end delimiter. So this get's rid of another problem - backslashes in file names (paths). In .NET Microsoft gave in to the norm, but you have the special case of @'string', where you allow single backslashes to mean just themselves.

The only other possibility in T-SQL is to SET QUOTED_IDENTIFIER OFF, as is mentioned in the link I gave above and also is not recommended as explained there.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[VijiKumara]

Thank you so much for the reply, Duan & Olaf. Unfortunately, it seems that the above query provided by Duan is not working as it is not returning any results for the insulations with special characters. Please refer to the screenshot.

Query 1: When I execute the query provided, it retrieves the results for insulations without single quote but not the one I am looking for.

Query 2: When I execute the query by putting only insulations which are having an apostrophe, I don't get any results

Query 3: When I execute Select * from the query, I could able to see results for all where it shows results for insulations with a single quote as well.

Quite confused. whats going wrong. Appreciate your help.

Regards,
Vijay

 

[dhookom]

All you needed to do was replace the one single quote with two. I was confused by your liberal usage of ()s. Since the field values don't contain the ()s try:

SELECT * FROM OPSD_TX_TEST_RESULTS WHERE INSULATION IN ('CH''', 'CL''', 'CH', 'CL')

It is always best to post data in tables rather than images. When posting images, they should be sufficiently large to allow old guys to see them without having to zoom in and squint ;-)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[VijiKumara]

There you go! You made my day sir. This query worked for me.

My apologies... I posted the image rather than the table. I will definitely follow that going forward. Thanks, once again.

 

[dhookom]

The image is better than nothing but it's difficult for some of us old guys to see. Some times the single quote might be curly [highlight #FCE94F]’[/highlight] or straight [highlight #FCE94F]'[/highlight]. The difference is subtle when viewing but huge when querying.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016