SUBSTR question
- From: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Feb 2009 14:23:57 -0600
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
Other related posts:
