Re: string matching with trailing space

In article <tppfjtn9ital3b_at_corp.supernews.com>, "Gary says...
>
>How does Oracle perform string matching with trailing spaces in the
>following example?
>
>CREATE TABLE a(
> dept CHAR(6);
>
>INSERT INTO a VALUES ('Labor ');
>
>SELECT * FROM aWHERE dept IN ('Labor');
>
>Will a string match occur?
>
>Thanks,
>Gary
>
>
>
>

In your example, yes, in general no.

Your example contained only character string constants which a real program won't -- it'll have bind variables. In SQL, the character string constant is "promoted" to the type of this it is being compared to -- so 'Labor' is really 'Labor ' when compared to a char(6).

Here is an example showing what I mean:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> CREATE TABLE a( dept CHAR(6) ); Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> INSERT INTO a VALUES ('Labor '); 1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> SELECT * FROM a WHERE dept IN ('Labor');

DEPT

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x varchar2(6) ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec :x := 'Labor'

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> SELECT * FROM a WHERE dept IN (:x);

no rows selected

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec :x := 'Labor '

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> SELECT * FROM a WHERE dept IN (:x);

DEPT

When using the bind variable with a VARCHAR type (the most common) the match will miss when the bind variable is less then 6 characters -- it'll hit when it is 6 characters.

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp