Implementation of the REVERSE function in DB2

Implementation of the REVERSE function in DB2

Some guys asked DB2 if there were any built-in reverse functions in the group. I tested that DB2 does have built-in functions, but Oracle and SQL server both have built-in functions. Let's take a look.

ORACLE:

SQL> select reverse ('20140901') from dual;

REVERSE (
--------
4321

SQL & gt; select reverse (12121) from dual;
Select reverse (12121) from dual
*
Row 3 has an error:
ORA-00932: Data Type inconsistent: Should be CHAR, but get NUMBER

It indicates that the reverse parameter in oracle is of the char type, and the returned value is also of the char type. Let's verify if this is the case.

SQL> select length (reverse ('20140901') from dual;

LENGTH (REVERSE ('20140901 '))
-----------------------
8

SQL> select reverse ('20140901') from dual;

REVERSE ('20140901 ')
----------------
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:

Implementation of the REVERSE function in DB2

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

C: \> sqlcmd-S kermart-U sa-P sa-d master
1> select reverse ('20140901 ');
2> go

----
4321

(One row is affected)
1> select reverse (1234); -- type conversion occurs, which should be visible in the execution plan
2> go

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

(One row is affected)

 

Based on oracle, SQL server has built-in reverse functions, and DB2 should also have its reverse function, because of some application scenarios.

CREATE OR REPLACE FUNCTION REVERSE
(
P1 varchar (200)
)
Returns varchar (200)
SPECIFIC "REVERSE"
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN

Declare v_str varchar (100) 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 ('20140901') from dual;

654321

Select reverse (1234) from dual; -- check the execution plan and you can see the type conversion.

4321

This article permanently updates the link address: