comparing 2 strings, weird behaviour with NLSSORT 1

[iza]

hi

I've got this very simple test in a query :
[tt]select * from ref_table r, loaded_data l where r.c_lon || ' ' || r.tl_lon = l.nom_voie[/tt]
- so far so good.

Except that in the reference table, as it's in french, there are some accents. And the loaded data are sometimes uppercase, sometimes not.
No problem, I solved it that way :
[tt]select * from ref_table r, loaded_data l where NLSSORT(r.c_lon, 'NLS_SORT = XFRENCH_AI') || ' ' || NLSSORT(r.tl_lon, 'NLS_SORT = XFRENCH_AI') = NLSSORT(l.nom_voie, 'NLS_SORT = XFRENCH_AI')[/tt]
- so far so good.
(not talking about performance issue here )

And here comes the quotes
[tt]select * from ref_table where c_lon || ' ' || tl_lon='RUE DE L'' ARBRE SEC'[/tt]
returns 1 row
[tt]select * from ref_table where UPPER(c_lon) || ' ' || UPPER(tl_lon)='RUE DE L'' ARBRE SEC'[/tt]
returns 1 row
[tt]select * from ref_table where UPPER(c_lon) || ' ' || NLSSORT(tl_lon, 'NLS_SORT = XFRENCH_AI')='RUE DE L'' ARBRE SEC'[/tt]
doesn't find any row

(the row i'm lookin for in ref_table contains <RUE DE L'> in c_lon and <ARBRE SEC> in tl_lon)

anyone to help me ???

 

[stefanhei]

Hi,
NLSSORT does not return the string you feed it but the string that is used to sort it according to the NLS-setting.

SQL> select NLSSORT('RUE DE L'' ARBRE SEC', 'NLS_SORT = XFRENCH_AI') from dual;

NLSSORT('RUEDEL''ARBRESEC','NLS_SORT=XFRENCH_AI')
--------------------------------------------------------------------------------
647328012328014B0114641964280169281E00020202020202020200270202020202020202020200

If you want to use it apply it to both sides of the equation.

select * from ref_table where NLSSORT(UPPER(c_lon) || ' ' || tl_lon, 'NLS_SORT = XFRENCH_AI')=NLSSORT('RUE DE L'' ARBRE SEC', 'NLS_SORT = XFRENCH_AI')

 

[iza]

brilliant !
I now understand the problem, and have a solution
thank you so much