0

How to select the column names of a table where column data contains '%' in the values?

Eg: In the table COMP there are two columns(Addr, Comp_Name) which has data containing '%' in its string. So my query should return those column_names(Addr, Comp_Name)

Supriya Lepakshi
  • 1
  • 1
  • Put double quotes around it. `SELECT "my_poorly_named_field_%" FROM mytable;` – JNevill Nov 07 '17 at 17:02
  • 3
    Possible duplicate of [How To Handle Table Column Named With Reserved Sql Keyword?](https://stackoverflow.com/questions/11629966/how-to-handle-table-column-named-with-reserved-sql-keyword) – JNevill Nov 07 '17 at 17:03

1 Answers1

1

I dont get you right but i thing this will help you

You can use the escape identifier

--Queries

-- for %
select * from test_a where col1 like '%\%%' escape '\';

--for _ 
select * from test_a where col1 like '%\_%' escape '\';
aljassi
  • 246
  • 2
  • 10
  • Hi Aljassi, I want to select the column of a table whose data has '%' – Supriya Lepakshi Nov 09 '17 at 04:40
  • hi, i dont know if it s possible with a simple select BUT you can make a function and solve that. – aljassi Nov 09 '17 at 08:00
  • 1. get all column name of the table --SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = 'YOUR_TABLE' AND data_type = 'VARCHAR2' order by 1; 2. with a cursor for each column name, count number of data set and save column name when count > 0 -- v_sql := ' select count(1) from test_a where '||column_name||' like ''%\%%'' escape ''\'' '; execute v_sql into a variable -- variable > 0 then save column name – aljassi Nov 09 '17 at 08:08