CODEWITHSUNDEEP
sqloracle-database
user2488578
user2488578

Reputation: 916

SQL : What does column <> '*.*' mean?

I have an existing code where I found below where condition on a table.

SELECT * FROM MYTABLE WHERE MYCOLUMN <> '*.*';

Is there a difference between MYCOLUMN <> '*.*' and MYCOLUMN <> '*';

What does MYCOLUMN <> '*.*' mean?

Upvotes: 2

Views: 327

Answers (3)

Alex Poole
Alex Poole

Reputation: 191275

Is there a difference between MYCOLUMN <> '*.*' and MYCOLUMN <> '*'

Yes, you are comparing different text literals with an inequality comparison condition.

The * has no special meaning. Specifically it is not a wildcard. From the patterns it looks like maybe your column holds filenames and you're doing some processing with the values later at OS level, where they might be wildcards. To the actual query they are not though.

The <> is the same as !=, so you are looking for either for rows where the entire content of the column is not the literal string '*.*' in the first case, or where the entire content of the column is not the literal string '*' in the second. No value can ever match both of those conditions, except null. (And null doesn't really match either - as it isn't equal to or not equal to anything; but the result of the comparison is 'unknown' so it isn't included in the result set).

As a demo:

create table t42(id number, mycolumn varchar2(20));
insert into t42 (id, mycolumn) values (1, '*');
insert into t42 (id, mycolumn) values (2, 'The*is embedded');
insert into t42 (id, mycolumn) values (3, 'The * is embedded');
insert into t42 (id, mycolumn) values (4, '*.*');
insert into t42 (id, mycolumn) values (5, 'The*.*is embedded');
insert into t42 (id, mycolumn) values (6, 'The *.* is embedded');
insert into t42 (id, mycolumn) values (7, 'No asterisk');
insert into t42 (id, mycolumn) values (8, null);

With the first condition only IDs 3 and 8 are excluded (3 because the condition is true, 8 because it is unknown):

select * from t42 where MYCOLUMN <> '*.*';

        ID MYCOLUMN            
---------- --------------------
         1 *                   
         2 The*is embedded     
         3 The * is embedded   
         5 The*.*is embedded   
         6 The *.* is embedded 
         7 No asterisk         

6 rows selected.

With the second condition only IDs 1 and 8 are excluded:

select * from t42 where MYCOLUMN <> '*';

        ID MYCOLUMN            
---------- --------------------
         2 The*is embedded     
         3 The * is embedded   
         4 *.*                 
         5 The*.*is embedded   
         6 The *.* is embedded 
         7 No asterisk         

6 rows selected.

Upvotes: 5

Afonso
Afonso

Reputation: 338

The where condition

MYCOLUMN <> '*.*'

is searching specifically for text diferent from '*.*'. This search will include '*' because '*' is different from '*.*'.

The where condition isn't checking regular expressions or wildcards.

To search text looking for text with * you can use the like condition - https://docs.oracle.com/cd/B13789_01/server.101/b10759/conditions016.htm.

Upvotes: 0

MT0
MT0

Reputation: 167981

What does MYCOLUMN <> '*.*' mean?

If the value in MYCOLUMN is not the exact string '*.*' and is not NULL then it will be matched by that condition.

* is not a wildcard character.

Is there a difference between MYCOLUMN <> '*.*' and MYCOLUMN <> '*'?

SQL Fiddle

Oracle 11g R2 Schema Setup: Some test data:

CREATE TABLE table_name ( mycolumn ) AS
SELECT '*' FROM DUAL UNION ALL
SELECT '*.*' FROM DUAL UNION ALL
SELECT 'a.b' FROM DUAL UNION ALL
SELECT 'a' FROM DUAL UNION ALL
SELECT NULL FROM DUAL;

Query 1:

SELECT *
FROM   table_name
WHERE  MYCOLUMN <> '*.*'

Results: Matches all the rows that are not identical to '*.*' or NULL.

| MYCOLUMN |
|----------|
|        * |
|      a.b |
|        a |

Query 2:

SELECT *
FROM   table_name
WHERE  MYCOLUMN <> '*'

Results: Matches all the rows that are not identical to '*' or NULL.

| MYCOLUMN |
|----------|
|      *.* |
|      a.b |
|        a |

Query 3: If you want to use wildcards in a match then use the LIKE operator with % as the wildcard (instead of *).

SELECT *
FROM   table_name
WHERE  MYCOLUMN NOT LIKE '%.%'

Results: Matches all the values that are not NULL and do not contain a . character:

| MYCOLUMN |
|----------|
|        * |
|        a |

Query 4:

SELECT *
FROM   table_name
WHERE  MYCOLUMN NOT LIKE '%'

Results: Matches all the values that are not NULL and do not contain zero-or-more characters (this will never match anything).

No data found.

Upvotes: 4

Related Questions