How to get the substring value from a column with data? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: How to get the substring value from a column with data?

  1. #11
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    Yeah _faq, pay attention to example above. What's up with that?
    DaPi used initcap for specific purpose.


  2. #12
    Join Date
    Feb 2004
    Posts
    77
    Sorry wrong thought process took me somewhere else only.

  3. #13
    Join Date
    Aug 2002
    Posts
    115
    Originally posted by DaPi
    translate(initcap(column),
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
    Will deal with any number of names - but I feel there must be a neater way . . .
    you are something..!!!

    PHP Code:
     create table test_to_del
     
    (
     
    name varchar2(20)
     );

     
    insert into test_to_del values ('saM len');
      
    insert into test_to_del values ('a b');
       
    insert into test_to_del values ('q v');

    select replace(translate(initcap(name),
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')
    from test_to_del;


    REPLACE(TRANSLATE(IN
    --------------------
    SL
    AB
    QV

    drop table test_to_del


  4. #14
    Join Date
    Mar 2002
    Posts
    38
    Originally posted by rshivagami
    select substr(names,1,1) || substr(names, instr(names,' ')+1,1) from tablename;

    You might want to add a decode before the second concatenation to check if there is a space.
    Thanks shivagami,
    but I am not able to get the correct output.can you pls show me how it works by using the decode.
    For instance samantha Mary - it should display the initial as SM
    but as per the above query it is displaying only S

    Thanks a lot
    Kishan
    SUROOP B

  5. #15
    Join Date
    Mar 2002
    Posts
    38
    Originally posted by aspdba
    you are something..!!!

    PHP Code:
     create table test_to_del
     
    (
     
    name varchar2(20)
     );

     
    insert into test_to_del values ('saM len');
      
    insert into test_to_del values ('a b');
       
    insert into test_to_del values ('q v');

    select replace(translate(initcap(name),
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')
    from test_to_del;


    REPLACE(TRANSLATE(IN
    --------------------
    SL
    AB
    QV

    drop table test_to_del

    Thanks DAPI. It's Excellent.

    Kishan
    SUROOP B

  6. #16
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    Kishan, are you saying mine and shivagami's code did not work? (they are identical)
    Are you sure you were not running some other code?
    d

Posting Permissions

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



Click Here to Expand Forum to Full Width