Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 1st, 2011, 01:57 AM
Authorized User
 
Join Date: Sep 2008
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default How to use oracle keyword in queries


I am calculating the length of a sentence using Length (scalar function), then I also used that keyword in the sentence, but I am having an error for all the queries below:


Error: Missing right parenthesis.

1-SELECT LENGTH('This sentence's length will be calculated') "Length of characters"
FROM dual;

2- SELECT LENGTH('This sentence's 'length' will be calculated') "Length of characters"
FROM dual;

3- SELECT LENGTH('This sentence's "length" will be calculated') "Length of characters"
FROM dual;

4-SELECT LENGTH('This sentence's "\length\" will be calculated') "Length of characters"
FROM dual;


5- SELECT LENGTH('This sentence's '\length\' will be calculated') "Length of characters"
FROM dual;

What should be the correct way to write this query?

Thanks in advance
__________________
How to do programming?
 
Old March 9th, 2011, 03:49 AM
Registered User
 
Join Date: Mar 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Your problem is not in the method length, but your alias is incorrect
try this "Length_of_characters" without whitespaces.
 
Old March 11th, 2011, 03:14 AM
Authorized User
 
Join Date: Sep 2008
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default Apostrophe use for enclosing strings, was the issue.

The apostrophe with sentence was causing problems as in Oracle; you enclose strings in single quotes.


Below query will work:

SELECT LENGTH('This sentence''s length will be calculated') "Length of characters"
FROM dual;

I found different solutions here:
http://www.techonthenet.com/oracle/questions/quotes.php

Thanks for your answer as well.
__________________
How to do programming?
 
Old April 8th, 2011, 06:28 AM
Authorized User
 
Join Date: Nov 2006
Posts: 31
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via ICQ to debasisdas Send a message via AIM to debasisdas
Default

You can also use the following .

Code:
SELECT LENGTH(q''[This sentence's length will be calculated]'') "Length of characters"
FROM dual;
Can put any number of single quotes inside the square brackets.

The above will work version 10 onwards only.
__________________
Regards
Debasis
 
Old April 11th, 2011, 05:37 AM
Authorized User
 
Join Date: Sep 2008
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default Thanks with a minor question

SELECT LENGTH(q''123'') "Length of characters"
FROM dual;

Answer:
Length of characters
----------------------
3
1 rows selected


It worked but what does q of (q’’123’’) means, Is this means quote?

as when I change it to any other alphabet it gives error of ‘missing right parenthesis’.
__________________
How to do programming?
 
Old April 12th, 2011, 12:42 AM
Authorized User
 
Join Date: Nov 2006
Posts: 31
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via ICQ to debasisdas Send a message via AIM to debasisdas
Default

Please follow the syntax as i have shown in my previous post.

Oracle 10g allows you to define your own string delimiters to remove the need to double up any single quotes. Any character that is not present in the string can be used as the delimiter:

Code:

syntax as per oracle 9i
DBMS_OUTPUT.put_line('This is Debasis''s string!');   

-- New syntax as per 10g. 
DBMS_OUTPUT.put_line(q'#This is Debasis's string!#'); 
DBMS_OUTPUT.put_line(q'[This is Debasis's string!]');
__________________
Regards
Debasis





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use IN statement with asc/desc in ORACLE/SQL queries? arbab SQL Language 1 September 20th, 2010 08:46 AM
How to use IN statement with asc/desc in ORACLE queries? arbab Oracle 1 September 20th, 2010 03:38 AM
Like keyword rajesh_css SQL Language 2 June 25th, 2009 08:13 AM
[Microsoft] [ODBC driver for oracle] [oracle] ORA-06413: connection not open dpkbahuguna Beginning VB 6 7 May 5th, 2009 12:31 AM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.