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: