SUBSTR question

From: Michael Schmitt <mschmitt_at_uchicago.edu>
Date: Fri, 27 Feb 2009 14:23:57 -0600
Message-ID: <E37E556CF8A6C44381D2DA9FC354D241F636672121_at_EVS03.ad.uchicago.edu>

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 - 14:23:57 CST