Home » SQL & PL/SQL » SQL & PL/SQL » Facing issue related to single quote('), in sql query.
icon3.gif  Facing issue related to single quote('), in sql query. [message #574015] Sat, 05 January 2013 04:57 Go to next message
NiteshKumar
Messages: 2
Registered: January 2013
Location: Pune, India
Junior Member
I have table say Messages. In which there is a column msg_text varchar2(900).
My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).
The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.
For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query

SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 10;

returns nothing.

Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query

SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 20;

returns character '?'.

Please suggest me the query to find out last character if second last character is singl-quote.
Thanks in advance... Smile
Re: Facing issue related to single quote('), in sql query. [message #574016 is a reply to message #574015] Sat, 05 January 2013 05:13 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Works fine for me; either I didn't understand the question or my test case is invalid, or something else, but I can't do what you claim. Would you mind posting YOUR SQL*Plus session (along with a test case)?

SQL> select * From v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> with test as
  2    (select 'Are you sure to continue?' col from dual union
  3     select 'Congratulations, you opted for ''A''.' from dual
  4    )
  5  select
  6    col,
  7    SUBSTR(col, LENGTH(col), LENGTH(col)) res,
  8    substr(col, -1) res2,
  9    regexp_substr(col, '.$') res3
 10  from test;

COL                                 RES   RES2  RES3
----------------------------------- ----- ----- -----
Are you sure to continue?           ?     ?     ?
Congratulations, you opted for 'A'. .     .     .

SQL>
Re: Facing issue related to single quote('), in sql query. [message #574017 is a reply to message #574016] Sat, 05 January 2013 05:37 Go to previous messageGo to next message
NiteshKumar
Messages: 2
Registered: January 2013
Location: Pune, India
Junior Member
I am trying the same query as you but getting different result. Below will give better picture.
Can you try with my sample message_text.

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jan 5 03:29:36 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> with test as (select msg_text col from online_messages where msg_code = 340257 union
2 select msg_text from online_messages where msg_code = 450410
3 )select
4 col,
5 SUBSTR(col, LENGTH(col), LENGTH(col)) res,
6 substr(col, -1) res2,
7 regexp_substr(col, '.$') res3
8 from test;
COL RES RES2 RES3
-------------------------------------------------------------------------------- ---- ----- -----
Entry with Default Indicator set to 'Yes' cannot be deleted. First set Default I
ndicator of other entry to 'Yes'.

Performing override action with no dealers in group list will delete all group ? ? ?
members for the selected dealers! Perform the delete action?

[Updated on: Sat, 05 January 2013 05:41]

Report message to a moderator

Re: Facing issue related to single quote('), in sql query. [message #574021 is a reply to message #574017] Sat, 05 January 2013 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Facing issue related to single quote('), in sql query. [message #574031 is a reply to message #574021] Sat, 05 January 2013 13:40 Go to previous message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Still no problem (however, your "test case" is a really bad test case, i.e. it is not a test case at all. You should have provided CREATE TABLE and INSERT INTO sample records. Or, if you use WITH factoring clause, it is useless to select from your tables as we don't have them so that is rather pointless.)

SQL> with test as
  2    (select 'Entry with Default Indicator set to ''Yes'' cannot be deleted. ' ||
  3            'First set Default Indicator of other entry to ''Yes''.' col from dual union
  4     select 'Performing override action with no dealers in group list will ' ||
  5            'delete all group members for the selected dealers! Perform the delete action?' from dual
  6    )
  7  select
  8    col,
  9    SUBSTR(col, LENGTH(col), LENGTH(col)) res,
 10    substr(col, -1) res2,
 11    regexp_substr(col, '.$') res3
 12  from test;

COL                                                RES   RES2  RES3
-------------------------------------------------- ----- ----- -----
Entry with Default Indicator set to 'Yes' cannot b .     .     .
e deleted. First set Default Indicator of other en
try to 'Yes'.

Performing override action with no dealers in grou ?     ?     ?
p list will delete all group members for the selec
ted dealers! Perform the delete action?


SQL>
Previous Topic: Check for create table permission
Next Topic: Please Make Query
Goto Forum:
  


Current Time: Sun Sep 21 04:06:41 CDT 2014

Total time taken to generate the page: 0.24738 seconds