CODEWITHSUNDEEP
sqloracle11goracle-sqldeveloper
user3223327
user3223327

Reputation: 7

How to get all the columns that has leading spaces in data?

I have a table with around 100 columns, I could get for a single column by using the following query

Select * from tes_tbl where col_1 like ' %'

But how can I retrieve all the columns in a single query

Im using oracle database

P.s: l'm a beginner to SQL

Upvotes: 0

Views: 63

Answers (2)

Liath
Liath

Reputation: 10191

You've not specified which sort of SQL so I'm going to assume MS SQL Server.

As others have pointed out you can generate a query along the lines of 

select * from tes_tbl where col_1 like '% ' OR col_2 like '% '

However you've mentioned that you've got a lot of columns to query. (This is the MSSQL specific bit) You can query Information Schema to get a list of the columns in your table:

select 'OR [' + COLUMN_NAME + '] like '' % ''' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tes_tbl'

This will give you a list of OR clauses for each of the columns. You can use these to build your query (replacing the part of the WHERE clause in the query above).

You can also use this trick to explicitly name the columns in the select which is often a good idea for anything other than ad-hoc queries.

Upvotes: 2

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

You can use OR operator as like below,

Select * from tes_tbl where col_1 like '%' OR col_2 like '%'

This will give you the result set which satisfies the conditions in WHERE clause

Upvotes: 0

Related Questions