The realization method of reverse function in DB2 _db2

ORACLE:

Sql> Select reverse (' 1234 ') from dual;

REVERSE (
--------
4321

Sql> Select Reverse (12121) from dual;
Select Reverse (12121) from dual
*
Line 1th Error:
ORA-00932: Data type inconsistency: CHAR is expected, but number is obtained

The argument for reverse in Oracle is the char type, and the return value is char, in the end, let's verify

Sql> Select Length (Reverse (' 1234 ')) from dual;

LENGTH (REVERSE (' 1234 '))
-----------------------
8

Sql> Select reverse (' 1234 ') from dual;

REVERSE (' 1234 ')
----------------
4321

If the return type is varchar, the length should be 4, so the return value is char. The parameter type should also be char.

SQL Server:

The parameter type varchar or nvarchar of the reverse function, and the return type is also varchar or nvarchar

C:\>sqlcmd-s kermart-u sa-p sa-d Master
1> Select reverse (' 1234 ');
2> Go

----
4321

(1 rows affected)
1> Select reverse (1234); --type conversions occur and should be visible from the execution plan
2> Go

------------
4321

(1 rows affected)

The reverse function is built into the Oracle,sql server, and DB2 should also have its reverse function, because there is a certain application scenario.

CREATE OR REPLACE FUNCTION REVERSE
(
 p1 varchar)
 RETURNS varchar (m) Specific
 "REVERSE"
 LANGUAGE SQL
 Deterministic
 NO EXTERNAL ACTION
 reads SQL DATA
BEGIN

 declare v_str varchar (m) default ';
 DECLARE V_index INTEGER;  --Define subscript
 SET v_index = Length (p1);
 while (V_index >= 1) do
  SET v_str = v_str| | SUBSTR (p1,v_index,1);
  SET v_index = v_index-1;
 End While;
 return v_str;
end@

Test

Select reverse (' 123456 ') from dual;

654321

Select reverse (1234) from dual; --looking at the execution plan, you should see the type conversion

4321