what does select lastname,dbo.GSTRANSLATE(LASTNAME) rslt from dbo.constituents where dbo.GSTRANSLATE(LASTNAME) = 'PENA'; give? On Fri, Feb 27, 2009 at 12:23 PM, Michael Schmitt <mschmitt@xxxxxxxxxxxx>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 > > > > >