Re: SUBSTR question

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 27 Feb 2009 15:07:29 -0700
Message-ID: <49A86421.9000405_at_evdbt.com>

Michael,

A couple extra bits of information which would be helpful for us...

• select * from nls_database_parameters
• descr dbo.constituents
• DDL for dbo.gstranslate function
  

After all, for all we know, the column LASTNAME might be defined NCHAR(3200) and NLS_NCHAR_CHARACTERSET using AL16UTF16 (multi-byte characters) for NLS_NCHAR_CHARACTERSET?  I'm not sure if that would produce these results, but it would certainly be relevant.

Thanks!

Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim_at_EvDBT.com
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt

Michael Schmitt wrote:

Hi All,

 

We are having an issue were the SUBSTR function is used and returns 1 row less than expected.  The select statement that the application runs uses a translate function and SUBSTR.  You can see the results of the queries below.  Can anyone explain why the SUBSTRB (or lack of SUBSTR) returns the 2 rows that we expect, while SUBSTR returns only the 1 row?  This is for a 9207 database.  I know when the database was created it was created with CHARACTER SET WE8MSWIN1252, which I am guessing is related.

 

Thanks,

Mike

 

SQL> select lastname  from dbo.constituents where dbo.GSTRANSLATE(LASTNAME) = 'PENA';

LASTNAME

--------------------

Pena

Peña

 

SQL> select lastname  from dbo.constituents where SUBSTR(dbo.GSTRANSLATE(LastName), 1, 3200) = 'PENA';

LASTNAME

--------------------

Pena

 

SQL>  select lastname  from dbo.constituents where SUBSTRB(dbo.GSTRANSLATE(LastName), 1, 3200) = 'PENA';

LASTNAME

----------------------------

Pena

Peña

 

 

-- http://www.freelists.org/webpage/oracle-l Received on Fri Feb 27 2009 - 16:07:29 CST