Convert SQL Server function into Oracle

Question

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.

Honestly, I'd try fixing the whole thing on both environments. A loop is going to be awfully slow on either RDBMS. — Thom A
– Thom A ♦, Commented Mar 16, 2021 at 18:58
Rather than all that looping and using UPPER and LOWER, have you considered just using INITCAP? — AndyDan
– AndyDan, Commented Mar 16, 2021 at 19:29
No, i didn't cause i need to convert exactly this function if possible.Thank — satcha
– satcha, Commented Mar 16, 2021 at 19:34

Littlefoot · Accepted Answer · 2021-03-16 19:46:55Z

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>

Collectives™ on Stack Overflow

Convert SQL Server function into Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related