Re: question about substr

On Jul 23, 11:49 am, haomiao <miaoh...@xxxxxxxx> wrote:
On 7 23 , 5 16 , sybrandb <sybra...@xxxxxxxxx> wrote:





On Jul 23, 10:49 am, haomiao <miaoh...@xxxxxxxx> wrote:

Hi

(1) When I run
select 1 from dual where '1 ' = substr('21',2,1)
no result return. There is spaces in the left operand.

(2) When I run
select 1 from dual where '1 ' = substr('21',
2,1) || ' '
1 result return

(3) And when I run
select 1 from dual where '1 ' = '1'
1 result return

What is the difference between (1)and(3)?
Can I find a better method than (2) , so I can eaily compare
without counting the spaces in '1 '

Thanks.
From the docs

Blank-Padded Comparison Semantics
If the two values have different lengths, then Oracle first adds
blanks to the end of the shorter one so their lengths are equal.
Oracle then compares the values character by character up to the first
character that differs. The value with the greater character in the
first differing position is considered greater. If two values have no
differing characters, then they are considered equal. This rule means
that two values are equal if they differ only in the number of
trailing blanks. Oracle uses blank-padded comparison semantics only
when both values in the comparison are either expressions of datatype
CHAR, NCHAR, text literals, or values returned by the USER function.

Your example 3 is made up by text literals, consequently the right
hand side of the expression will be padded.
Your example 1 will not be padded

What is the datatype of substr('21',2,1)? text literals?
What is the datatype of text literals?

How can I convert substr('21',2,1) to CHAR?





You actually did read the quote from the docs?
Doesn't look like.
Question 2 is irrelevant.
Repeating part of the quote...
Oracle uses blank-padded comparison semantics only
when both values in the comparison are either expressions of datatype
CHAR, NCHAR, *text literals*, or values returned by the USER function
Text literals do not have a datatype.
A substr returns a varchar2. Hence is your original statement (1)
variable length semantics are being used.
If you really want to convert a varchar2 to char as opposed to the
much more elegant solution of LTRIMming the left hand side expression,
to get rid of the redundant spaces, look up the CAST function in your
documentation.

--
Sybrand Bakker
Senior Oracle DBA

.

Relevant Pages

  • Oracle Character Conversion Bugs (#NISR2122004G)
    ... Oracle 10g/AS on all operating systems ... Due to character conversion problems in Oracle 10g with Oracle's Application ... database server as SYS. ...
    (NT-Bugtraq)
  • Re: Storing Queries in Tables
    ... Either you should follow the method Oracle is using to store SQL ... removed all the indents from the query and then they have worked. ... of report, date, other qualifier etc. ... brings me back to character sets. ...
    (comp.databases.oracle.misc)
  • Oracle Character Conversion Bugs (#NISR2122004G)
    ... Oracle 10g/AS on all operating systems ... Due to character conversion problems in Oracle 10g with Oracle's Application ... database server as SYS. ...
    (Bugtraq)
  • [VulnWatch] Oracle Character Conversion Bugs (#NISR2122004G)
    ... Oracle 10g/AS on all operating systems ... Due to character conversion problems in Oracle 10g with Oracle's Application ... database server as SYS. ...
    (VulnWatch)
  • Re: comparing a date?
    ... How do I compare a date to be equal to a particular day? ... incorrectly -- probably due in part to the examples included in most Oracle ... datatype, with sub-second granularity. ... include a time element -- if a user had been created exactly at midnight on ...
    (comp.databases.oracle.misc)