Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: nls_initcap usage

    I'm using nls_initcap to present names nicely, but got this problem :

    Name field : KIM LARSEN II

    using nls_initcap gives : Kim Larsen Ii

    I wish to have : Kim Larsen II

    Is it possible and how ?

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    select replace('Kim Larson Ii','Ii','II') from dual
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    First, I wouldn't keep "name" in one column. Separate first name, last name, and suffix into separate columns.

    If you cannot do that, you could always do something like this:

    Code:
    new_name_var := NLS_INITCAP( SUBSTR( name_var,  1, INSTR( name_var, ' ', 1, 2) ) ) || SUBSTR( name_var, INSTR( name_var, ' ', 1, 2 ) );
    INSTR( name_var, ' ', 1, 2 ) --> starting at position 1, finds the second occurence of ' ' (space) in name_var

    Hope this helps or at least steers you in the right direction.

    JoeB

  4. #4
    Join Date
    Mar 2004
    Posts
    4
    joebednarz,

    Agreed, but that option is out of my hands, it's a standard systems database and I can't change it.

    I'm quite new to Oracle and PlSql so I was hoping there might be an optional way of doing the initcap or perhaps an 'do not change these combinations' list.

    Thanks for the reply, you too dbtoo2001

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •