CODEWITHSUNDEEP
sqloracle-databaseselect
artemis
artemis

Reputation: 7261

Oracle SQL -- Select all but entries that are blank or have spaces

Hello and good afternoon,

I am trying to compose a query to select a number of Customer IDs. Sometimes, for whatever reason, the Customer ID (PK) might have a blank Customer Name, or a Customer Name with 4 or 5 spaces in it, but no number or letters (A name can be any string of numbers or letters, sometimes "-" as well).

How can I filter it out? My current query looks like this in addition to my other WHERE clauses. I need to ensure CustomerName isn't just a bunch of spaces, or empty. It needs to have some alphanumeric text, or a -: 

   SELECT cust.CustomerID, cust.CustomerName
   FROM cust
   WHERE cust.StartDate Between '01-Jun-2017' AND '01-Sep-2017' AND cust.CustomerID Like '%100%' Or cust.CustomerID Like '%200%'

However, I want something to look like this:

   AND CustomerName NOT LIKE '%  %' OR != ""

Any advice would be greatly appreciated!

Upvotes: 0

Views: 1725

Answers (5)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

Best might be to use a regular expression:

AND NOT REGEXP_LIKE(CustomerName, '^[[:space:]]*$')

Upvotes: 0

fhossfel
fhossfel

Reputation: 2181

I assume with your query you want to do something like this:

AND (CustomerName NOT LIKE '%  %' OR CustomerName != '')

Warning! This is not going to give you a compilation error but it is not going to work work as you expect. Oracle is going to interpret '' as NULL and nothing is unequal to NULL (nothing is equal to NULL either). Therefore the second part of your condition is always going to be false which makes it useless. To achieve what you want you have to use 

OR CustomerName IS NOT NULL

or as described by other above:

OR TRIM(CustomerName) IS NOT NULL

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

Something like this will solve the problem as stated:

where customerId is not null
and customerId > ''
and customerId = , replace(customerId, ' ', '') -- this takes care of the spaces

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

I think you just want parentheses in the right places:

SELECT cust.CustomerID, cust.CustomerName
FROM cust
WHERE cust.StartDate Between date '2017-06-01' and date '2017-09-01' and 
      (cust.CustomerID Like '%100%' Or cust.CustomerID Like '%200%')

The like pattern requires that the customer id has at least 100 or 200, so spaces and empty strings are not permitted.

You might also notice that I prefer date constants using ISO standard formats YYYY-MM-DD.

Or, if you prefer:

SELECT cust.CustomerID, cust.CustomerName
FROM cust
WHERE cust.StartDate Between date '2017-06-01' and date '2017-09-01' and 
      regexp_like(cust.CustomerID, '[12]00')

Upvotes: 0

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

I think that the below function will help you to solve the problem:

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )

Also you probably need to wrap the above around 

LENGTH( string1 )

Don't hesitate to ask me for any further clarifications!

Ted

Upvotes: 2

Related Questions