Oracle Database - regexp_replace function in pl/sql

Asked By Noman Nasir on 31-Mar-10 07:11 AM
HI

I just want to extract initials from a string using regexp_replace function in pl/sql
e.g.

String = 'Firstname Lastname'

function should return only 'FL'.

Please anyone can help??

Thanks
Noman
Anand Malli replied to Noman Nasir on 05-Apr-10 08:53 AM
Hi Noman,


please create a function with below code:

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(40), @Delimiter NVARCHAR(2))       
returns Nvarchar(40)
AS        
BEGIN        
 
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @Output Nvarchar(40)
 
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)
SET @output=''
 
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,1)
 
SET @output = @output + @NextString  -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END
 
 
RETURN @output      
END

Execute the code with Following query

select dbo.split('FirstName LastName',' ')

It will give you output as "FL".
Sekhara Shiris Chinta replied to Noman Nasir on 04-May-10 08:34 AM
create funtion fun1(str1 varchar2)
  return varchar2
as
  str_out varchar2(20);
  i number :=1;
begin
 
  str_out := substr(str1,1,1)
  while(str1(i))
  loop
    if str1(i) =' ' then
     str_out := str_out || str1(i+1);
    end if;
    i :=i +1;
  end loop;
  return str_out;
 
end;