Shulou(Shulou.com)06/01 Report--

1.decode

Meaning explanation:

Decode (condition, value 1, return value 1, value 2, return value 2. Value n, return value n, default)

This function has the following meaning:

IF condition = value 1 THEN

RETURN (translation value 1)

ELSIF condition = value 2 THEN

RETURN (translation value 2)

.

ELSIF condition = value n THEN

RETURN (translation value n)

ELSE

RETURN (default)

END IF

Decode (field or field operation, value 1, value 2, value 3)

The result of this function is that when the value of the field or field operation is equal to the value 1, the function returns the value 2, otherwise the value 3

Of course, values 1, 2, and 3 can also be expressions, which makes some sql statements much easier.

2.to_date date string converted to date

To_date (# staDate#,'yyyy/MM/dd')

3.to_char date converted to string

To_char (a.d_alternationdate, 'yyyy-mm-dd') f_date

4.INSTR

(source string, target string, starting position, matching sequence number)

In Oracle/PLSQL, the instr function returns the position of the string to be intercepted in the source string. Retrieve it only once, that is, from the beginning of the character

It ends at the end of the character.

The syntax is as follows:

Instr (string1, string2 [, start_position [, nth_appearance]])

Parameter analysis:

String1

The source string in which to find.

String2

The string to find in string1.

Start_position

Represents the location of the string1 to start searching. This parameter is optional, if omitted defaults to 1. The string index starts at 1. If this parameter is positive, retrieve from left to right, and if this parameter is negative, retrieve from right to left, returning the starting index of the string you want to find in the source string.

Nth_appearance

Represents to look for the string2 that appears for several times. This parameter is optional. If omitted, it defaults to 1. If it is negative, the system will report an error.

Note:

If String2 is not found in String1, the instr function returns 0. 0.

Example:

SELECT instr ('syranmo','s') FROM dual;-returns 1

SELECT instr ('syranmo','ra') FROM dual;-returns 3

1 SELECT instr ('syran mo','a',1,2) FROM dual;-returns 0

(according to the condition, because an appears only once, the fourth parameter 2, that is, the position of the second occurrence of a, obviously the second time does not appear again, so the result returns 0. Notice that a space is also a character! )

SELECT instr ('syranmo','an',-1,1) FROM dual;-returns 4

(even if you count from right to left, the position of the index still depends on the position of the first letter on the left of 'an', so it returns 4)

SELECT instr ('abc','d') FROM dual;-returns 0

Note: you can also use this function to check whether String2 is included in String1. If 0 is returned, it is not included, otherwise it is included.

For the above mentioned, we can use the instr function like this. Take a look at the following example:

If I have a piece of information on which are the job numbers of some employees (field: CODE), but now I want to find out the situation of all their employees, such as name, department, occupation, and so on, here are two employees whose job numbers are 'A10001', respectively. If staff is assumed to be an employee table, the normal practice is as follows:

1 2 SELECT code, name, dept, occupation FROM staff WHERE code IN ('A10001')

Or:

SELECT code, name, dept, occupation FROM staff WHERE code = 'A10001' OR code =' A10002'

Sometimes there are more employees, and we feel more troublesome about that, so we think, can we export it all at once? At this point you can use the instr function, as follows:

SELECT code, name, dept, occupation FROM staff WHERE instr ('A10001 for A10002) > 0

The result of the query is the same, so only two single quotation marks are used before and after, which is relatively convenient.

There is another usage, as follows:

SELECT code, name, dept, occupation FROM staff WHERE instr (code, '001') > 0

Equivalent to

SELECT code, name, dept, occupation FROM staff WHERE code LIKE'1%'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.