SUBSTR question

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: