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!
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