CODEWITHSUNDEEP
sqlsql-serveroracle-databasefunctionplsql
satcha
satcha

Reputation: 129

Convert SQL Server function into Oracle

This is my SQL Server function and I'm trying to convert uppercase to propercase and its works successfully in SQL Server but not in Oracle, could someone help me to convert the below code:

CREATE FUNCTION CamelCase(@Text as VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
 DECLARE @Index INT;
 DECLARE @CurChar CHAR(1);
 DECLARE @Reset BIT;
 DECLARE @Ret VARCHAR(8000);

 IF @Text IS NULL
   RETURN NULL;

 SELECT @Reset = 1, 
        @Index = 1,
        @Ret = '';

 WHILE (@Index <= len(@Text))

 SELECT @CurChar = substring(@Text, @Index, 1),
        @Ret = @Ret + CASE WHEN @Reset = 1 THEN UPPER(@CurChar) ELSE LOWER(@CurChar) END,
        @Reset = CASE WHEN @CurChar like '[a-zA-Z]' THEN 0 ELSE 1 END,
        @Index = @Index + 1
RETURN @Ret
END

I tried to translate it into Oracle function code which is shown below:

create or replace function  CamelCase(Text in VARCHAR2)
RETURN VARCHAR2
IS
 Ret VARCHAR2(8000):= '';
 Index NUMBER:= 1;
 CurChar CHAR0;
 Resets BIT := 1;
BEGIN    
 WHILE (Index <= LENGTH(Text))
 loop
    CurChar := SUBSTR(Text, Index, 1);
    if (Reset = 1) then 
          ret:= UPPER(CurChar) ;
        ELSE 
         ret:= LOWER(CurChar);
    end if ;
    if (CurChar like '[a-zA-Z]') then 
    reset:=  0;
    else 
      reset:=  1;
    end if ;
    Index := Index + 1;
 end loop;      
 RETURN Ret;
END

But it's seems wrong, can someone help me please for conversion.

Upvotes: 0

Views: 149

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

Would this do? See comments within code.

SQL> create or replace function f_proper (par_text in varchar2)
  2    return varchar2
  3  is
  4    l_this varchar2(1);   -- current character
  5    l_prev varchar2(1);   -- previous character
  6    l_res varchar2(200);  -- resulting string
  7  begin
  8    if par_text is null then
  9       return null;
 10    end if;
 11
 12    for i in 1 .. length(par_text) loop
 13      l_this := substr(par_text, i, 1);
 14      l_prev := substr(par_text, i - 1, 1);
 15
 16      l_res := l_res ||
 17        -- If I'm on the first character of the string OR
 18        -- previous character is a space and current character is not a space
 19        -- then return UPPERCASE of the current character.
 20        -- Else, return lowercase of the current character.
 21        case when i = 1 or
 22                  (l_prev  = ' ' and l_this <> ' ')
 23             then upper(l_this)
 24             else lower(l_this)
 25        end;
 26    end loop;
 27
 28    return l_res;
 29  end;
 30  /

Function created.

Testing:

SQL> select f_proper('little  fo ot') r1,
  2         f_proper('I tried to translate it into Oracle') r2,
  3         f_proper('ABC') r3,
  4         f_proper(null) r4
  5  from dual;

R1              R2                                  R3  R4
--------------- ----------------------------------- --- ---
Little  Fo Ot   I Tried To Translate It Into Oracle Abc

SQL>

Or, without reinventing the wheel, use built-in initcap function:

SQL> select initcap('little  fo ot') r1,
  2         initcap('I tried to translate it into Oracle') r2,
  3         initcap('ABC') r3,
  4         initcap(null) r4
  5  from dual;

R1              R2                                  R3  R4
--------------- ----------------------------------- --- ---
Little  Fo Ot   I Tried To Translate It Into Oracle Abc

SQL>

Upvotes: 1

Related Questions