asked on 3/4/2016

Query all columns in a table for specific characters

Is is possible to query against an entire table in Oracle and throw the results for only the fields/columns that contain the specified criteria? For example
I want to
select *
from table
where any field in any column contains a comma, question mark, hash tag, or exclamation point

Oracle Database SQL

Last Comment

Sean Stuber

3/4/2016

Sean Stuber

3/4/2016

a query like that won't be efficient but you can do it with simple concatenation of the columns and checking each character

select * from table
where col1||col2||col3||col4||col5 .... like '%,%'
or col1||col2||col3||col4||col5 .... like '%?%'
or col1||col2||col3||col4||col5 .... like '%#%'
or col1||col2||col3||col4||col5 .... like '%!%'

Basssque

3/4/2016

ASKER

Can the where statement be consolidated?
For example, If I need to check 100 columns for 20 different like statements or want to add a field, its a lot to edit

ASKER CERTIFIED SOLUTION

Sean Stuber

3/4/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial