1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get 1st letter of every word in city?

Discussion in 'SQL PL/SQL' started by Vicky, Jul 27, 2016.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Suppose, I'm having 'New York City' in my City Column. I want to get only 'NYC' as Output:

    I did like..

    Code (SQL):
    SELECT substr(regexp_substr('New York City','[^ ]+',1,level),1,1) FROM dual
    CONNECT BY level<=(LENGTH(regexp_replace('New York City','[^ ]+')))+1
    Output:
    ---------
    N
    Y
    C

    Expected Output:
    ----------------------
    NYC


    I hope there will some better way to achieve this.. Btw I'm using Oracle 10g.;)
     
  2. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    select substr(' New York City',instr(' New York City',' ',1,1)+1,1)||substr(' New York City',instr(' New York City',' ',1,2)+1,1)||substr
    (' New York City',instr(' New York City',' ',1,3)+1,1) as sid from dual;
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Sid. But is der anny way to achieve it Dynamically, becoz, the spaces between the words differs for other entries and some of them has single word..
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  5. VISHAL BADUKALE

    VISHAL BADUKALE Newly Initiated

    Messages:
    1
    Likes Received:
    2
    Trophy Points:
    40
    Location:
    pune
    Try this,

    select
    translate(
    initcap('New York City')
    , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'
    , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    ) TEST
    from dual
     
    jagadekara and Vicky like this.
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai

    Thanks so much Vishal.. That Really Helps!:)