Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the functions in Mysql in dm. It is very detailed and has certain reference value. If you are interested, you must finish reading it.

First, problems

1. The FIND_IN_SET function exists in mysql, but not in dm. The solution to manually creating the FIND_IN_SET () function can be implemented in dm and called for use. But when the result set is large, the efficiency is very low. It can be used when the amount of data is small. At that time, when the amount of data is relatively large, the FIND_IN_SET function is used, and the problem of character set truncation will occur when using dm. Based on the above questions. Use the instr () function instead of the FIND_IN_SET function in dm. It can solve the problems of inefficiency and string truncation.

2. There is a group_concat () function in mysql. But not in dm. You can use the LISTAGG/LISTAGG2 set function of wm_concat or dm in oracle instead. The LISTAGG/LISTAGG2 set function in Dm is used the same way as the LISTAGG/LISTAGG2 set function in oracle.

Second, the definition of FIND_IN_SET function 1. Mysql:

The syntax explanation of the find_in_set function in the MySQL manual:

FIND_IN_SET (str,strlist). The strlist parameter of the string to be queried by str is the result of (str) contained in the query field (strlist), for example, the delimited field name (1 str), and the returned result is null or record if the string str is in the string list strlist consisting of N subchains. Then a string list with a range of values from 1 to N is a string consisting of subchains separated by symbols. If the first parameter is a constant string and the second is a type SET column, the FIND_IN_SET () function is optimized, using bits to calculate that if str is not in strlist or strlist is an empty string, the return value is 0 if any parameter is NULL. The return value is NULL. This function will not work properly if the first argument contains a comma (,).

The result is: 2 because b is placed in the position of 2 in the strlist set to start select FIND_IN_SET from 1 ('1century,' 1'); return is 1 the strlist collection is a little special at this time only a string requires that the previous string must be in the latter string collection before returning a number greater than 0. Select FIND_IN_SET ('2minutes,' 1jue 2'); returns 2. Select FIND_IN_SET ('6percent,' 1'); returns 0. Str does not exist in strlist, so 0 is returned.

2. Custom find_in_set ()

Create or replace

Function FIND_IN_SET2

(

Piv_str1 varchar2

Piv_str2 varchar2)

Return int deterministic

As

Rt int default 0

Begin

With

An as

(

Select

Regexp_substr (sqlstr,'[^,] +', 1, level) rval

Rownum rid

From

(

Select piv_str2 sqlstr from dual

)

Connect by level 0 THEN

-- intercept the field str before the first delimiter

Str:= substr (piv_str,1,l_idx-1)

Judge whether str and piv_str1 are equal, equal res=1 and end the cycle

IF str = piv_str1 THEN

Res:= loopIndex

EXIT

END IF

Piv_str: = substr (piv_str,l_idx+length (p_sep))

ELSE

-- when there is no separator in the intercepted piv_str, judge whether piv_str and piv_str1 are equal or equal to res=1

IF piv_str = piv_str1 THEN

Res:= loopIndex

END IF

Jump out of the cycle, regardless of whether the end is equal or not

EXIT

END IF

END LOOP

-- end the cycle

END IF

-- return res

RETURN res

END FIND_IN_SET

4. The function calls the case find_in_set () to query the data and truncate the string.

The effect of using instr instead is as follows

The effect of using instr instead is as follows

The effect of using instr instead is as follows

Third, group_concat () function

1. The definition of group_concat () function in mysql.

Function: concatenate the values in the same group generated by group by and return a string result.

Syntax: group_concat ([distinct] the field to be connected [order by sort field asc/desc] [separator 'delimiter'])

Note: duplicate values can be excluded by using distinct; if you want to sort the values in the results, you can use the order by clause; separator is a string value and defaults to a comma.

2. Use wm_concat or dm's LISTAGG/LISTAGG2 set function 1, wm_concat in dm

Introduction: its function is introduced in Oracle 10g. In 10g version, it returns string type, and in 11g version, it returns clob type.

The parameters in parentheses are columns and can be a collection of multiple columns, which means you are free to merge strings with'| |'in parentheses.

Usage 1: Select aaa, wmsys.wm_concat (bbb | | ('| | ccc | |')') from table group by aaa

Usage 2: Select aaa, wmsys.wm_concat (bbb | |'('| ccc | |')) over (partition by aaa) from table

For example:

2. Brief introduction of LISTAGG ()

Introduction: its function is introduced in the Oracle 11g version, which concatenates the grouped data in strings according to a certain sort.

Where "[,]" represents the separator of a string concatenation, and if you choose to use [over (partition by)], it will become an analysis function.

Usage 1: SELECT aaa, listagg (bbb,',') within GROUP (ORDER BY aaa) FROM table GROUP BY aaa

Usage 2: SELECT aaa, listagg (bbb,',') within GROUP (ORDER BY aaa) over (partition by aaa) FROM table

For example: listagg (ORGAN_CODE,',') within group (order by1) listagg

The above is all the content of the article "how functions in Mysql are used in dm". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.