Re: VARCHAR2: NULL value vs. empty string

From: VC <boston103_at_hotmail.com>
Date: Sat, 13 Dec 2003 06:09:37 GMT
Message-ID: <B2yCb.379697$ao4.1268776@attbi_s51>

"Chris" <cs123._no_spam__at_telstra.com> wrote in message news:2CxCb.50465$aT.20559_at_news-server.bigpond.net.au...
> Just out of interest sake, how do you index a zero length string?
>
>

From SQL'92 standard:

<quote>

1. If <character substring function> is specified, then:
2. Let C be the value of the <character value expression>, let LC be the length of C, and let S be the value of the <start position>.
3. If <string length> is specified, then let L be the value of <string length> and let E be S+L. Otherwise, let E be the larger of LC + 1 and S.

<... skipped ...>

            e) Case:

              i) If S is greater than LC or if E is less than 1, then the
                 result of the <character substring function> is a zero-
                 length string.

</quote>

The answer to your question is: zero-length string ( S=1, LC=0 -> '').

Rgds. Received on Sat Dec 13 2003 - 00:09:37 CST