×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

comparing 2 strings, weird behaviour with NLSSORT

comparing 2 strings, weird behaviour with NLSSORT

comparing 2 strings, weird behaviour with NLSSORT

(OP)
hi :)

I've got this very simple test in a query :
select * from ref_table r, loaded_data l where r.c_lon || ' ' || r.tl_lon = l.nom_voie
- 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 :
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')
- so far so good.
(not talking about performance issue here noevil)

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

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

lookaround anyone to help me ???

RE: comparing 2 strings, weird behaviour with NLSSORT

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

CODE

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.

CODE

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') 

RE: comparing 2 strings, weird behaviour with NLSSORT

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close