concatenating ' (single quote) character

[bcdixit]

Hi,
I want to concatenate the single quote character in a SQL select statement.

for eg...for
select
'select columnname, columntype from dbc.Columns where tablename = ''trim(TableName)'' and databasename = ''BI_STG'' and columntype <> ''TS'';' AS a
from dbc.tables
where DatabaseName = 'BI_STG';

I am getting the following result back

a
-----------
select columnname, columntype from dbc.Columns where tablename = 'trim(TableName)' and databasename = 'BI_STG' and columntype <> 'TS';


I need trim(TableName) to be replaced with the actual table name with single quote character placed at the front and at the end.

therefore I would like to see..

a
-----------
select columnname, columntype from dbc.Columns where tablename = 'BILL_STG' and databasename = 'BI_STG' and columntype <> 'TS';


Please help
Thanks

 

[bcdixit]

After some trial and errors and a little bit of google search by my colleague ....
select
'select columnname, columntype from dbc.Columns where tablename = '||''''||trim(TableName)||''''||' and databasename = ''BI_STG'' and columntype <> ''TS'';' AS a
from dbc.tables
where DatabaseName = 'BI_STG' ;

this produces the expected result..

a
----------
select columnname, columntype from dbc.Columns where tablename = 'BILL_STG' and databasename = 'BI_STG' and columntype <> 'TS';


But..I still dont understand why we need 4 single quote before and after the concatenate operator.

 

[RogerCruse]

Hi bcdixit,
You could have written it simpler like this -

select
'select columnname, columntype from dbc.Columns where tablename = ''' ||trim(TableName)|| ''' and databasename = ''BI_STG'' and columntype <> ''TS'';' AS a
from dbc.tables
where DatabaseName = 'BI_STG' ;

Roger...

 

[bcdixit]

thanks Roger