Functionally, you should be able to use
SELECT column_name
FROM table_name
WHERE TRIM(column_name) IS NULL
The problem there is that an index on COLUMN_NAME would not be used. You would need to have a function-based index on TRIM(column_name) if that is a selective condition.
Answer from Justin Cave on Stack OverflowFunctionally, you should be able to use
SELECT column_name
FROM table_name
WHERE TRIM(column_name) IS NULL
The problem there is that an index on COLUMN_NAME would not be used. You would need to have a function-based index on TRIM(column_name) if that is a selective condition.
SELECT column_name from table_name
WHERE RTRIM(ISNULL(column_name, '')) LIKE ''
ISNULL(column_name, '') will return '' if column_name is NULL, otherwise it will return column_name.
UPDATE
In Oracle, you can use NVL to achieve the same results.
SELECT column_name from table_name
WHERE RTRIM(NVL(column_name, '')) LIKE ''
Select *
From Table
Where (col is null or col = '')
Or
Select *
From Table
Where IsNull(col, '') = ''
If you need it in SELECT section can use like this.
SELECT ct.ID,
ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM [dbo].[CustomerTable] ct
You can replace the null with your substitution value.
If you only want to match "" as an empty string
WHERE DATALENGTH(COLUMN) > 0
If you want to count any string consisting entirely of spaces as empty
WHERE COLUMN <> ''
Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.
CREATE TABLE T
(
C VARCHAR(10)
);
INSERT INTO T
VALUES ('A'),
(''),
(' '),
(NULL);
SELECT *
FROM T
WHERE C <> ''
Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.
SQL Fiddle
WHERE NULLIF(your_column, '') IS NOT NULL
Nowadays (4.5 years on), to make it easier for a human to read, I would just use
WHERE your_column <> ''
While there is a temptation to make the null check explicit...
WHERE your_column <> ''
AND your_column IS NOT NULL
...as @Martin Smith demonstrates in the accepted answer, it doesn't really add anything (and I personally shun SQL nulls entirely nowadays, so it wouldn't apply to me anyway!).
I have two tables. Each has "2.5 mL fill in a 4 mL container" string entered in a field. The only problem is they need to match for our process at work to work. But somehow they don't match and I can't figure out how to check for which string has something different from the other one. Thanks in advance for your help friends.
I would say that NULL is the correct choice for "no email address". There are many "invalid" email addresses, and "" (empty string) is just one. For example "foo" is not a valid email address, "a@b@c" is not valid and so on. So just because "" is not a valid email address is no reason to use it as the "no email address" value.
I think you're right in saying that "" is not the correct way to say "I don't have a value for this column". "" is a value.
An example of where "" might be a valid value, separate to NULL could be a person's middle name. Not every one has a middle name, so you need to differentiate between "no middle name" ("" - empty string) and "I don't know if this person has a middle name or not" (NULL). There's probably many other examples where an empty string is still a valid value for a column.
While agreeing with the above comments, I would add this argument as a primary motivation:
- It is obvious to any programmer looking at a database that a field marked NULL is an Optional field. (i.e. the record doesn't require data for that column)
- If you mark a field NOT NULL, any programmer should intuitively assume that it is a Required field.
- In a field that allows nulls, programmers should expect to see nulls rather than empty strings.
For the sake of Self-Documenting Intuitive Coding, use NULL instead of empty strings.
I think this:
SELECT
ISNULL(NULLIF(listing.Offer_Text, ''), company.Offer_Text) AS Offer_Text
FROM ...
is the most elegant solution.
And to break it down a bit in pseudo code:
// a) NULLIF:
if (listing.Offer_Text == '')
temp := null;
else
temp := listing.Offer_Text; // may now be null or non-null, but not ''
// b) ISNULL:
if (temp is null)
result := true;
else
result := false;
SELECT
CASE WHEN LEN(listing.OfferText) > 0 THEN listing.OfferText
ELSE COALESCE(Company.OfferText, '') END
AS Offer_Text,
...
In this example, if listing.OfferText is NULL, the LEN() function should also return NULL, but that's still not > 0.
Update
I've learned some things in the 5 1/2 years since posting this, and do it much differently now:
COALESCE(NULLIF(listing.OfferText,''), Company.OfferText, '')
This is similar to the accepted answer, but it also has a fallback in case Company.OfferText is also null. None of the other current answers using NULLIF() also do this.
Give this a try:
In SQL server you can use this:
SELECT * FROM MYTABLE
WHERE CHARINDEX(' ',ColumnName) > 0;
If you are using Oracle you can use this:
SELECT * FROM MYTABLE
WHERE INSTR(ColumnName,' ') > 0;
Essentially in this query it finds the character position containing first space from the column values and when it finds the first space in it the index value should be greater than 1 and it should display all the records based on that.
try put another '%', this may solve your problem.
select * from MyTable where ColumnName like '% %'