Return a string with comma and ' in it.

[RustyAfro]

Hello,

I need the below SQL statment to return a string "'a','b','c'" exactly as shown as a field. Oracle see's the commas as separate fields.

Is there some Oracle function that will return whatever is passed in as a literal string instead of trying to parse special characters like ' and , ?

Select
('a','b','c') as MyField
From Dual

I appreciate any help!

 

[SantaMufasa]

Rusty,

One of the very annoying characteristics of Oracle's SQL implementation is that the only method of delimiting literals is with a matching pair of single quotes. Therefore to embed a single quote in a literal string, you use two successive single quotes to produce one remaining single quote [or apostrophe] (within the outer, delimiting single quotes):

Select '(''a'',''b'',''c'')' as MyField From Dual;

MYFIELD
-------------
('a','b','c')

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[RustyAfro]

Hi Mufasa,

I was afraid you would say that :-(. The below returns what I want, but I have very little control over the practical need behind this question so cannot add more single quotes unless it can be done through a function call.

SELECT ('''a'',''b'',''c''') as MyField
From Dual

A 3rd party SQL parser (think something like Crystal Reports) prompts a user for values and injects it's response into a where clause on a field, ie:

MyField In @prompt(Enter list of values) becomes:
MyField In ('a','b','c')

But if I try to bring the prompt answer into the select statement it becomes:

Select
('a','b','c')
From Dual

I could wrap the resulting answer into a function like this:
AnyOracleFunction('a','b','c')

But all oracle functions seem to treat the comma's as a variable.

Ah well, I can't think of anyway around this at the moment. Thanks for the help though!

 

[SantaMufasa]

Your code, above, passes Oracle's syntax checking and produces results.:

SELECT ('''a'',''b'',''c''') as MyField
From Dual
/

MYFIELD
-----------
'a','b','c'

How do you want the results to differ from these?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

BTW, when you say:

I could wrap the resulting answer into a function like this:

AnyOracleFunction('a','b','c')

...That function invocation would be passing three separate arguments to the function...is that what you wanted/intended?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[RustyAfro]

Sorry, let me clarify.

I meant to say that "SELECT ('''a'',''b'',''c''')" returns what I want, but I cannot actually implement a solution like that because I have no means to add additional single quotes to force oracle to treat the quotes and commas as part of the entire string. Unless there is a function that can do so when the argument to any such function is ('a','b','c')

I was hoping to find something like this: SomeOracleFunction('a','b','c') that would force oracle to treat the string 'a','b','c' inside the parenthesis as a string instead of trying to parse special characters like comma and single quotes. Your right however, that any function that I can find thus far treats 'a','b','c' as three arguments. The exception being Concat which can take 'a','b' and return "ab". I would even settle for that if it could take more then 2 arguments :-/

 

[jaxtell]

Is oracle the only piece to the puzzle? I think that most programming languages that interact with oracle would take care of this sort of issue. In java, I don't need to worry about cleansing strings before passing them to the database. I think the information you've provided might be an oversimplification of what you need, and is preventing us from giving you the best answer.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[RustyAfro]

Hi Jax, Your right, it is a simplification of the practical need. True, if it was a programming environment I could easily take care of this. But I'm working with an enterprise reporting tool that gives no option to scrub the string before it is passed to Oracle to execute. The tool never meant for a user response to a prompt to be used in the way I am attempting. So far, single values response ('A') are fine, but not multi because of the issue above.

I'm changing strategies and working on a different approach as I can't see anyway to trick oracle into accepting a string 'a','b','c' in the select statement. Thanks for the help though!

 

[Beilstwh]

tell your users to type it as

a,b,c,d,e,f

when crystal reports inserts it in as a string, it should look like

'a,b,c,d,e,f'

Bill
Lead Application Developer
New York State, USA