Implementation of the REVERSE function in DB2

Source: Internet
Author: User

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.