Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: Stripping out Initials out of Name field.

    Hi AGAIN!!

    Guys....got a problem, How do strip out the initials out of the Name field.
    Example Name field "Duncan T R", I want to take the "T R" out and put it in the initials field. How do I do that?Please give me a example.

    THANKS!!!
    Shaun

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    It depends on what is the logic to find out the last 2 names. If it is the last 3 characters, use substring.

    If its something else, you would need to provide that logic.
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Sorry for typo "last 2 names" ... I meant logic to find out initials in the entire name.

    Originally posted by cmasharma
    It depends on what is the logic to find out the last 2 names. If it is the last 3 characters, use substring.

    If its something else, you would need to provide that logic.
    Oracle can do wonders !

  4. #4
    Join Date
    Jan 2004
    Posts
    37
    select substr(name,(instr(name,' ')+1),(length(name)-instr(name,' '))) from dual

  5. #5
    Join Date
    Jan 2004
    Posts
    7
    THANKS!!!

    Works great but what happens if it is "T R Duncan" the Initials is not at the back but at the front??

  6. #6
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Bottomline is, you need a logic to decide what makes the initials...

    if you say, any single character / set of single characters makes the initials...? then we can suggest a SQL to do so ...

    base question is, what is the logic for determining the initials..??

    Originally posted by ShaunR
    THANKS!!!

    Works great but what happens if it is "T R Duncan" the Initials is not at the back but at the front??
    Oracle can do wonders !

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    One possible rule might be: parse the name into "words" using space as delimiter:

    'Duncan T R' -> 'Duncan', 'T', 'R'

    'T R Duncan' -> 'T', 'R', 'Duncan'

    Then throw away everything that doesn't have length of 1, leaving 'T', 'R' in both the above cases.

    Of couse, now you'll say: What if name is 'T.R.Duncan' or 'Duncan T. R." or "Duncan, TR"!!!

  8. #8
    Join Date
    Jan 2004
    Posts
    37
    why don't you put the initials and the surname in two diffrent fields????
    Then you can manupulate with it the way you want.
    Right ? Any problems in this?

  9. #9
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    I have written a function that will return all the single characters in your name field.

    Create this function and then if you execute
    select getInitials(name) from emp;

    If your table has data
    T Duncan R
    Duncan T R
    T R Duncan

    All will return the output as
    'T R'
    Hope this helps..

    Please change the size of name & initial field in this function as per your requirement.

    Andrew, is there is function in Oracle to split a string at a delimiter as against the looping that I have done?

    Code:
    create or replace function getInitials(vInName in varchar) return varchar as
    	nPosition number;
    	vInitial varchar2(10);
    	vName varchar2(80 );
    begin
    
    	vInitial := ' ';
    	vName := trim(vInName);
    	
    	loop
    		nPosition := instr(vName, ' ');
    		if nPosition = 0 THEN
    			IF length(vName) = 1 THEN
    				vInitial := vInitial ||vName;
    			END IF;
    			exit;
    		end if;
    		
    		if length(substr(vName, 1, nPosition-1)) = 1 then
    			vInitial := vInitial || substr(vName, 1, nPosition-1) || ' ';
    		end if;
    		vName := substr(vName, nPosition+1, length(vName));
    	end loop;
    	
    	return trim(vInitial);
    end;
    /
    Originally posted by andrewst
    One possible rule might be: parse the name into "words" using space as delimiter:

    'Duncan T R' -> 'Duncan', 'T', 'R'

    'T R Duncan' -> 'T', 'R', 'Duncan'

    Then throw away everything that doesn't have length of 1, leaving 'T', 'R' in both the above cases.

    Of couse, now you'll say: What if name is 'T.R.Duncan' or 'Duncan T. R." or "Duncan, TR"!!!
    Oracle can do wonders !

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by cmasharma
    Andrew, is there is function in Oracle to split a string at a delimiter as against the looping that I have done?
    No, but it is useful often enough that I have written my own package to do it. There is a procedure in DBMS_UTILITY called COMMA_TO_TABLE that does it, but it only works for values that would be valid as Oracle object names:
    Code:
      1  declare
      2    t dbms_utility.uncl_array;
      3    n integer;
      4  begin
      5    dbms_utility.comma_to_table( 'aaa,bbb,cccc', n, t );
      6    for i in 1..n loop
      7      dbms_output.put_line(t(i));
      8    end loop;
      9* end;
    SQL> /
    aaa
    bbb
    cccc
    
    PL/SQL procedure successfully completed.
    But:
    Code:
      1  declare
      2    t dbms_utility.uncl_array;
      3    n integer;
      4  begin
      5    dbms_utility.comma_to_table( '111,222,333', n, t );
      6    for i in 1..n loop
      7      dbms_output.put_line(t(i));
      8    end loop;
      9* end;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-00931: missing identifier
    ORA-06512: at "SYS.DBMS_UTILITY", line 79
    ORA-06512: at "SYS.DBMS_UTILITY", line 108
    ORA-06512: at line 5

Posting Permissions

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