Knowledge Point record when data is migrated from mysql to oracle (1), mysqloracle

Knowledge Point record when data is migrated from mysql to oracle (1), mysqloracle

Some SQL statements have been modified during the recent migration of data from mysql to oracle, and some knowledge points are recorded here:

References: https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring-index

1. [mysql] find_in_set:

Syntax: find_in_set (str, strList) -----> Return the index position of the first argument within the second argument

Function: search for the string 'strlist' in the string list and return the index position of 'str. String to be searched by str, which is a string separated by commas.

Definition:

1. Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

If the str string is in the strlist consisting of N strings, a value ranging from 1 to N is returned.

2. A string list is a string composed of substrings separated by, characters.

A string list is a string consisting of substrings separated by strings.

3. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET () function is optimized to use bit arithmetic.

If the first parameter is a constant string and the second parameter is a SET column, the FIND_IN_SET () function is optimized to use bitwise operations.

4. Returns 0 if str is not in strlist or if strlist is the empty string.

If the str string is not in the strlist of the string or the strlist of the string is an empty string, 0 is returned.

5. Returns NULL if either argument is NULL.

If any parameter is null, null is returned.

6. This function does not work properly if the first argument contains a comma (,) character.

If the first parameter contains a comma (,), the function cannot work normally.

Practical application:

A field value in A table is composed of two parts connected with "|", for example, A | B. A indicates a c-type number and B indicates A D-type number.

When querying, the query is based on the C-type number and D-type number as two conditions:

Use find_in_str (?, REPLACE ('a | B ',' | ',', ') can be used for queries. If this function is not available in oracle, we can use the following methods:

Use the REGEXP_SUBSTR (String, pattern, position, occurrence, modifier) function in oracle to obtain the required part and compare it with the condition value for query:

For example, REGEXP_SUBSTR ('A', '[^ |] +', 1, 1) =? As a query condition for C-type numbers, REGEXP_SUBSTR ('B', '[^ |] +', 1, 2) =? As the query condition for the D type number.

2. [mysql] substring_index)

Syntax: substring_index (str, delim, count) ------> Return a substring from a string before the specified number of occurrences of the delimiter

Function: returns a substring from a string before the specified delimiter appears. String intercepted by str, delim separator, and count separator.

Definition:

1. Returns the substring from stringstrBeforecountOccurrences of the delimiterdelim.

Returns the substring In the str string before the delimiter appears.

2. IfcountIs positive, everything to the left of the final delimiter (counting from the left) is returned.

If count is positive, all content on the left of the final separator is returned (counted from the left ). That is, count from the left side to get the content on the left side of the count separator.

3. IfcountIs negative, everything to the right of the final delimiter (counting from the right) is returned.

If count is negative, all content on the right of the final separator is returned (counted from the right ). That is, count from the right side to get the content on the right side of the count separator.

4,SUBSTRING_INDEX()Performs a case-sensitive match when searchingdelim.

SUBSTRING_INDEX () performs case-sensitive matching when searching for delim.

Practical application:

A field value in A table is composed of two parts connected with "|", for example, A | B. A indicates a c-type number and B indicates A D-type number.

During the query, C and D numbers are queried and displayed:

In mysql, we can use the substring_index (str, delim, count) function to achieve this. in oracle, we can still use REGEXP_SUBSTR (String, pattern, position, occurrence, modifier) to achieve this requirement.

3. [mysql] UUID:

In practical applications, we sometimes use the UUID () function to implement unique values.

In mysql, we can use select replace (uuid (), '-', '') as uuid; To obtain unique values. In oracle, we must use: select lower (sys_guid () as uuid from dual; To obtain