Common functions in DB2 (constantly updated in ...)

Source: Internet
Author: User
Tags db2 rand rtrim
Common functions in DB2:
Function name Explanation of the function numbers Example
SUBSTR (EXP1,EXP2) SUBSTR (EXP1,EXP2,EXP3) Returns the EXP1 string starting at EXP2, with a substring length of EXP3 SELECT SUBSTR (' CDNJFDJFJD ', 5) from Bsempms; SELECT SUBSTR (' CDNJFDJFJD ', 5,2) from Bsempms;
ROUND (EXP1,EXP2) Returns the rounded value starting at the EXP2 position to the right of the EXP1 decimal point. SELECT ROUND (2345.6789,2) from Bsempms
REPLACE (EXP1,EXP2,EXP3) Replace all the EXP2 in EXP1 with EXP3 SELECT CHAR (REPLACE (' romandd ', ' NDD ', ' CCB '), ten) from Bsempms
REPEAT (EXP1,EXP2) Returns the string after EXP1 repeats EXP2 times SELECT CHAR (REPEAT (' REPEAT ', 3), +) from Bsempms
RAND () Returns the random floating-point number between 0 and 1 SELECT RAND () from Bsempms
Posstr (EXP1,EXP2) Returns the position of the EXP2 in EXP1 SELECT posstr (' abcdefgh ', ' D ') from Bsempms
Nullif (EXP1,EXP2) NULL if EXP1=EXP2, otherwise EXP1

COALESCE (Col_1, ")

field Null value substitution function

1. The input parameter is a character type, and is allowed to be empty, you can use COALESCE (InputParameter, ') to convert null to ';

2. Input type is integral type, and allowed to be empty, can use COALESCE (inputparameter,0), turn idling into 0;

3.

The input type is integer and is non-null and does not require the use of the COALESCE function to directly use is NULL for a nonempty judgment.

LENGTH (EXP)

Get the length of the string exp Values length (' Test '); --Output 4

CAST (EXP1 as EXP2)

Type conversion function: Convert EXP1 to EXP2 type

1. Convert characters to integers: cast (' 2 ' As Integer);

2. Convert to String: Cast (current date as char (20));

3. Convert to floating point number: Cast (' 22.02 ' as Decimal (4,2))

LTRIM (EXP)

RTRIM (EXP)

DB2 provides the LTrim function and the RTrim function, but does not provide the trim function, if you want to remove the spaces at both ends of the character, I'm sorry, I have to call it in LTrim (RTrim ()).

1. Remove left space: LTRIM (' ABC ');

2. Remove the right space: LTRIM (' ABC ');

3. Remove the left and right spaces: LTRIM (RTRIM (' ABC '));

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.