string with single quotes

Database

string with single quotes

Hi
I have a field that is a string and i need to insert a string along with the single quotes surrounding it can this be done?

the data should be 'Feroz'

***********************
same way can we view string data with single quotes surrounding it
for example if i query
select date
i can see the date.
can the date be displayed as '2007-04-12'
i.e with the single quotes.

Thanks
4 REPLIES

Re: string with single quotes

Double the single-quote inside a string to get a literal single quote. For example:

select '''foo''' as foo;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

foo
-----
'foo'

select '''' || date || '''' as "date";

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

date
----------
'07/04/12'

Re: string with single quotes

If you are using bteq, you can also try just plain format command.

SELECT CURRENT_DATE (FORMAT '''YYYY-MM-DD''');

Re: string with single quotes

CREATE USER "XXXXXXX" FROM "BUSINESS" AS

PERM = 0

PASSWORD = password

SPOOL = 0

TEMPORARY = 0

STARTUP = ''

NO FALLBACK

NO BEFORE JOURNAL

NO AFTER JOURNAL

COLLATION = HOST

DEFAULT CHARACTER SET LATIN

DATEFORM = INTEGERDATE

TIME ZONE = NULL

PROFILE = "Business_User_Managed"

DEFAULT ROLE = ALL;

I am trying to create a stored procedure to create users.

In the procedure, i am trying to do the following things

1) Set Sql_String =

'

CREATE USER "XXXXXXX" FROM "BUSINESS" AS

PERM = 0

PASSWORD = password

SPOOL = 0

TEMPORARY = 0

STARTUP = ''

NO FALLBACK

NO BEFORE JOURNAL

NO AFTER JOURNAL

COLLATION = HOST

DEFAULT CHARACTER SET LATIN

DATEFORM = INTEGERDATE

TIME ZONE = NULL

PROFILE = "Business_User_Managed"

DEFAULT ROLE = ALL;

';

and then CALL DBC.SysExecSQL(:Sql_String);

I am losing one of the quotes in the STARTUP statement. I have tried a zillion different ways to quote these quotes to no avail.

Can someone tell me how this is done. I have tried '''', etc. What are the correct number of quotes to quote a quote?

2) I am trying to pass the user_id XXXXXXX in as an argument. This too is failing.

I call the procedure create_user (IN_USER_ID) as char(7) input paramater as

CALL CREATE_USER

(

'XXXXXXX'

);

I am trying to achieve the following in the first line of the script:

being: CREATE USER ":IN_USER_ID" FROM "BUSINESS" AS

 

This too is causing an error.

I have been working on this for quite some time to no avail. Is there anyone that can give me a working example?

N/A

Re: string with single quotes

You need to double the single quote for the STARTUP and concat the double quote to Sql_String:

'CREATE USER "' || :IN_USER_ID || '" FROM "BUSINESS" AS
PERM = 0
PASSWORD = password
SPOOL = 0
TEMPORARY = 0
STARTUP = ''''
NO FALLBACK
NO BEFORE JOURNAL
NO AFTER JOURNAL
COLLATION = HOST
DEFAULT CHARACTER SET LATIN
DATEFORM = INTEGERDATE
TIME ZONE = NULL
PROFILE = "Business_User_Managed"
DEFAULT ROLE = ALL;'