View previous topic :: View next topic
|
Author |
Message |
gvshruthi
New User
Joined: 05 Jan 2009 Posts: 4 Location: bangalore
|
|
|
|
Hi All,
I am creating an SQL stored procedure. I am stuck with the concatenation of strings and variables used in the stored procedure.
The concatenation is as follows:
SET SQL_QUERY =
'SELECT ' || <var1> || ' AS VAR1,' || ''' || <var2> || '''' ||| ' AS VAR2'
|| ' FROM ' || <var3> || ' WHERE ' || <var4> || ';'
var1, var 2, var 3 and var 4 are variables that contain values fetched from other tables in the stored procedure.
I need the result in the SQL_QUERY variable as follows:
SELECT 1 AS VAR1,'server' AS VAR2 FROM table WHERE condition;
But I get the result as follows:
SELECT 1 AS VAR1,' || <var2> || ' AS VAR2 FROM table WHERE condition;
Can anyone let me know how would I have to concatenate apostrophes (') in the final string. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
It's pretty simple:
a string starts with a single quote '
and ends at the next single quote UNLESS
there are two single quotes.
In that case the string continues (the two single quotes become one single quote within the string) .
This is yours :
SET SQL_QUERY = 'SELECT ' || <var1> || ' AS VAR1,' || ''' || <var2> || ''' || ' AS VAR2' || ' FROM ' || <var3> || ' WHERE ' || <var4> || ';'
What you want is
SELECT ' || <var1> || ' AS VAR1,''' || <var2> || ''' AS VAR2' || ' FROM ' || <var3> || ' WHERE ' || <var4> || ';' |
|
Back to top |
|
|
gvshruthi
New User
Joined: 05 Jan 2009 Posts: 4 Location: bangalore
|
|
|
|
Thanks a lot GuyC.
I wanted the result as:
SELECT 1 AS VAR1,'server' AS VAR2 FROM table WHERE condition;
To string one apostrophe, we need to concatenate it the following way:
''''|| <var2> || '''' || ' AS VAR2
This is how it worked for me. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
|| '''' || ' AS VAR2 ' is equivalent to || ''' AS VAR2 '
like
|| 'A' || 'B' is equivalent to || 'AB' |
|
Back to top |
|
|
|