I am trying to get the last alphabet from an input and then gives a particular response from that. like input "morning" have G at end and i want output as "G-Text". so any input letter having last letter at end gives the output as "_ - TEXT". I tried with REGEX_LIKE but nothing. Also in same function i need code for below examples-

I can check the function with this input data, for example, " fdsfdsfdd23" - > the result of the function will be "3 - ODD" or jsdhfjsdhjfhdksf -> the result will be "f - TEXT"

please help.

code

error- number or argument called for =

i tried everything. Syntax error maybe

CodePudding user response：

Try this - it will put one of the suffixes ('-TEXT', '-ZERO', '-ODD', '-EVEN') to the last character of the input parameter depending on the type....

create or replace Function Last_Char(p_input_char VarChar2) RETURN VarChar2 Is
BEGIN
    Declare
        mChar   VarChar2(1) := '';
        mSfx    VarChar2(20) := '';
    Begin
        If p_input_char Is Null Then 
            RETURN 'NO_PARAM_ERR'; 
        End If;
        --
        mChar := SubStr(p_input_char, Length(p_input_char), 1);
        If mChar = '0' Then 
            mSfx := '-ZERO';
        ElsIf InStr('1,3,5,7,9', mChar) > 0 Then
            mSfx := '-ODD';
        ElsIf InStr('2,4,6,8', mChar) > 0 Then
            mSfx := '-EVEN';
        Else
            mSfx := '-TEXT';
        End If;
        RETURN Upper(mChar) || mSfx;
    End;
END Last_Char

... test ...

SET SERVEROUTPUT ON
Begin
  DBMS_OUTPUT.PUT_LINE('jhkjd087');
  DBMS_OUTPUT.PUT_LINE('jhkjd08');
  DBMS_OUTPUT.PUT_LINE('jhkjd0');
  DBMS_OUTPUT.PUT_LINE('jhkjd');
End;
--  Results
--  
--  anonymous block completed
--  7-ODD
--  8-EVEN
--  0-ZERO
--  D-TEXT

CodePudding user response：

Just for the fun of it, here's a solution that does it in a query. The WITH clause just sets up test data (A great way to supply sample data for questions you ask). ALWAYS put in unexpected values in your test data. Especially if the source is user-entered data. They can and will hit any crazy key combination you'll ever imagine. Note you can't have an if statement in a case statement. Also as a matter of best practice always catch unexpected values in case statements.

WITH tbl(ID, str) AS (
  SELECT 1, 'abcd' FROM dual UNION ALL
  SELECT 2, 'abc1' FROM dual UNION ALL
  SELECT 3, 'abc2' FROM dual UNION ALL
  SELECT 4, 'abc0' FROM dual UNION ALL
  SELECT 5, NULL FROM dual UNION ALL
  SELECT 6, 'abc'||CHR(09) FROM dual -- a tab
)
SELECT ID, str, 
       CASE 
         WHEN str IS NULL THEN
           'NULL'
         -- If last character is a zero
         WHEN SUBSTR(str, '-1', '1') = '0' THEN 
           '0 - Zero'
         -- If last character is an alpha character
         WHEN REGEXP_LIKE(str, '.*[[:alpha:]]$') THEN
           SUBSTR(str, '-1', '1') || ' - Text'
         -- If last character is a number, test for even/odd by seeing if it's
         -- evenly divisible by 2
         WHEN REGEXP_LIKE(str, '.*[[:digit:]]$') THEN
           CASE MOD(SUBSTR(str, '-1', '1'), 2) 
             WHEN 0 THEN SUBSTR(str, '-1', '1') || ' - Even'
             WHEN 1 THEN SUBSTR(str, '-1', '1') || ' - Odd'
             ELSE 'Something went horribly wrong'
           END
         -- Something not already caught.  Non-printable character, show yourself!
         ELSE 'Hex value - '|| RAWTOHEX(UTL_RAW.CAST_TO_RAW(SUBSTR(str, '-1', '1'))) 
       END as last_char
FROM tbl;

  ID STR  LAST_CHAR      
---- ---- ---------------
   1 abcd d - Text       
   2 abc1 1 - Odd        
   3 abc2 2 - Even       
   4 abc0 0 - Zero       
   5      NULL           
   6 abc     Hex value - 09 

6 rows selected.