CODEWITHSUNDEEP
sqloracle-database
Moudiz
Moudiz

Reputation: 7377

how to get columns have long spaces ( multiple spaces)

I have the below structure(' ' refers to empty spaces):

name   description
---------------------
a         yes
b       '       '
c         ' '
d          null

I am searching for a query that give me the rows contain empty spaces, asked for the below result .

 name   description
    ---------------------
    b       '       '
    c         ' '

this query select * from tab1 where description =' '; will give me only c, in my query I have many values have long spaces.

Upvotes: 0

Views: 1131

Answers (4)

Sentinel
Sentinel

Reputation: 6449

with this basic query:

with sample_data(name, description) as (
  select 'a', 'yes' from dual union all 
  select 'b', '       ' from dual union all
  select 'c', ' ' from dual union all
  select 'd', null from dual
)
select * 
  from sample_data

you can pick and choose among the following where clauses to get your desired results:

where regexp_like(description,'[ ]')); -- at least one space in the string
where regexp_like(description,'[ ]{2,')); -- two or more consecutive spaces
where regexp_like(description,'^[ ]+$')); -- just spaces of any length
where regexp_like(description,'^[ ]{2,}')); -- just paces of length 2 or more

if you want any white space character (e.g. tabs, vertical tabs, non blanking spaces, etc.) you can replace the single space character class [ ] with this [[:space:]] named character class in any of the above where clauses.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Use LIKE operator

SELECT * 
FROM tab1 
WHERE description LIKE ' %'

Upvotes: 0

Stefan Yordanov
Stefan Yordanov

Reputation: 666

You can user REGEXP_LIKE:

with src as (select 'a' as name,'yes' as description from dual
union all 
select 'b','       ' from dual
union all
select 'c',' ' from dual
union all
select 'd',null from dual)
select * from src where regexp_like(description,'^[ ]+$'))

Edited: added regexp_like(description,'^[ ]+$') to take into account only descriptions with spaces. If there is a description in the format ' s ', ' s' or 's ' it will not be selected.

Upvotes: 2

Hari Chaudhary
Hari Chaudhary

Reputation: 650

Use TRIM function to trim the spaces.

select * from tab1 where TRIM(description) IS NULL;

I have not tested it but it should work.

Upvotes: 1

Related Questions