CODEWITHSUNDEEP
asp.netoracle-database
FashionHouseJewelry.com
FashionHouseJewelry.com

Reputation: 431

Use parameter in like clause in oracle

I'm trying to build a search page in ASP.NET that allows me to search for table names in oracle. When I place the sql in the sqldatasource control it doesn't recognize the parameter :Tablename. How do I need to rewrite this so that it works?

SELECT Owner, Table_name, Num_Rows, Tablespace_name
FROM all_tables
WHERE trim(upper(table_name)) LIKE trim(upper('%:TableName%'))

Upvotes: 19

Views: 30895

Answers (2)

Jeff
Jeff

Reputation: 8148

For those that might be interested: For SQL Server embedding the % into the parameter's text like this works: (The other method described above doesn't)

WHERE trim(upper(table_name)) LIKE trim(upper(@TableName))

Upvotes: 1

Adam Paynter
Adam Paynter

Reputation: 46918

Can you replace

'%:TableName%'

with

'%' || :TableName || '%'

?

Upvotes: 57

Related Questions