What does the 'like' expression look like for a zero length string?

select * from x where something Like %%

In article <b8a5b877-d51b-4742-bb6e-cd3376dda29e (AT) 8g2000prb (DOT) googlegroups.com>, SpreadTooThin <bjobrien62 (AT) gmail (DOT) com> wrote:
Think about it a little bit...

If you're trying to match a zero-length string, why would you use LIKE at all?
There's no "pattern" to match, only an exact value. So the answer to your
question is,
SELECT * FROM x WHERE something = '';

On Mar 1, 4:17*pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

You need to be careful with inserting "emtpy" strings. If you used
the \where something=''/ would return no rows as it equates empty
string '' as NULL. You would get the correct results if you used
"where something is NULL;" My "MySQL" system is currently unavailable
to test how it behaves.

On 3/1/2011 7:37 PM, onedbguru wrote:
Incorrect. '' is an empty string, not a NULL value. If you insert an
empty string, WHERE something IS NULL will NOT return the row. However,
WHERE something = '' will return the row.

If you want a null value in a field, use NULL. It is specifically the
lack of a value; an empty string is a value. Two different things.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

onedbguru wrote:
IME NULL is not the same as a zero length string at all.

Upate..set (field1=NULL) seems to give a different value from set(field1='')

I have had some weird an interesting bugs because of this.

On Mar 1, 8:05*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
All that I was saying was that you need to be careful... Not all
database engines treat empty string the same as null. Not saying that
it is correct behavior, but there are db engines out there that an
empty string is equal to NULL value. There are tons of articles
written on the treatment of ES vs NULL and you can look them up.

In MySQL, it would be interesting to dump the data block to see what
is actually stored on disk to differentiate ES vs NULL. In some
{unnamed} database engines, no value is stored as HEX 00 (aka
NULL).

On Mar 2, 4:48*pm, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote:
Right.. That's why I wanted it to be LIKE '%%' and not = ''

In article <2f1a0dcb-34ff-479f-9458-0d6707af887b (AT) v16g2000vbq (DOT) googlegroups.com>, onedbguru <onedbguru (AT) yahoo (DOT) com> wrote:
In fact, most don't.

In article <d2eff590-5193-4a65-9632-eb4a111a261a (AT) q12g2000prb (DOT) googlegroups.com>, SpreadTooThin <bjobrien62 (AT) gmail (DOT) com> wrote:
NULL. The only way to match NULL is with the IS NULL predicate.

Moreover, no LIKE predicate containing '%' will ever match *only* an empty
string, either.

mysql> create table foo (bar decimal, fubar varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into foo values (1, '');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (2, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (3, 'abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo where fubar like '%';
+------+-------+
2 rows in set (0.01 sec)

mysql> select * from foo where fubar = '';
+------+-------+
1 row in set (0.00 sec)

mysql> select * from foo where fubar is null;
+------+-------+
1 row in set (0.00 sec)

On 3/2/2011 6:48 PM, onedbguru wrote:
Any database which adheres to the SQL standards does.

No, hex(00) is NOT the same as NULL. As anyone who proports himself to
be a "db guru" should know.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================