How to use oracle keyword in queries

arbab · February 1st, 2011, 01:57 AM

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

Gerecs · March 9th, 2011, 03:49 AM

Your problem is not in the method length, but your alias is incorrect
try this "Length_of_characters" without whitespaces.

arbab · March 11th, 2011, 03:14 AM

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.

debasisdas · April 8th, 2011, 06:28 AM

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.

arbab · April 11th, 2011, 05:37 AM

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â.

debasisdas · April 12th, 2011, 12:42 AM

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!]');