Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Collating sequence question?

Re: Collating sequence question?

From: Maximus <qweqwe_at_qwqwewq.com>
Date: Mon, 11 Aug 2003 03:46:51 GMT
Message-ID: <LkEZa.693203$ro6.14411887@news2.calgary.shaw.ca>

"Hemant Shah" <shah_at_typhoon.xnet.com> wrote in message news:bguc5d$dr0$1_at_flood.xnet.com...
> While stranded on information super highway John Russell wrote:
> :)On Thu, 7 Aug 2003 15:29:19 +0000 (UTC), Hemant Shah
> :)<shah_at_typhoon.xnet.com> wrote:
> :)
> :)>
> :)>Folks,
> :)>
> :)> Can I setup Oracle server/database so that it does not use any
collating
> :)> sequence (i.e. do a byte by byte comparisions)?
> :)
> :)The NLS_SORT parameter can be set to the value BINARY:
> :)
>

:)http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch4.ht m#1004875
> :)
> :)Haven't had hands-on experience to know how pervasive the results are.
> :)I see ORDER BY clauses explicitly calling the NLSSORT function when
> :)they want to ensure a particular linguistic order:
> :)
>

:)http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functi ons82a.htm#84558
> :)
> :)John
> :)--
> :)Photo gallery: http://www.pbase.com/john_russell/
>
>
> I tried NLS_SORT = BINARY but it does not work.
>
> Here is my problem.
>
> I have a table whose primary column is char(10), the value of this
character
> is always 10 characters long and may contain non printable characters
> including 0x00.
>
> Given a partial key I want to fetch all the rows where the value of the
column
> is greater than NULL padded key.
>
> Exmaple: If the partial key value is 'AAA' then I would like to fetch all
the
> rows where the key is >= 'AAA0x000x000x000x000x000x000x00' and key <=
> '0xff0xff0xff0xff0xff0xff0xff0xff0xff0xff'
>
> I declare cursor as follows:
>
> EXEC SQL DECLARE LnamePat CURSOR FOR
> SELECT lname, rec FROM lidpown.mytable
> WHERE lname >= :TmpKey AND lname <= :HighValues
> ORDER BY NLSSORT(lname, 'NLS_SORT=BINARY');

Try this:

EXEC SQL DECLARE LnamePat CURSOR FOR

    SELECT NLSSORT(lname, 'NLS_SORT=BINARY') as x, rec FROM lidpown.mytable     WHERE x >= NLSSORT(:TmpKey, 'NLS_SORT=BINARY') AND x <= NLSSORT(:HighValues, 'NLS_SORT=BINARY')

    ORDER BY NLSSORT(lname, 'NLS_SORT=BINARY'); Received on Sun Aug 10 2003 - 22:46:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US