Oracle Functions-Merge, replace, splice, intercept, find

1. Merge function wm_concat (column)
Wm_concat (column name), which separates the column values by the "," number and displays them as a row. If the column value is in Chinese, choose another way: Wm_concat (to_char (column name))

For example, the following example:

Perform the following Sql:select Id,wm_concat (TO_CHAR (name)) name from the TestTable group by ID; The following results can be obtained

2. Replace the function replace (original field, "original field old content", "original field new Content",)

Perform the following sql:select id,name,replace (num, ' Ten ', ' 5 ') NUM0 from TestTable; The following results can be obtained

3. Stitching string function concat (Strings 1, string 2)

For string concatenation, each database has a corresponding method-----Mysql:concat () oracle:concat (), | | SQL Server: +

The syntax for CONCAT () is as follows:CONCAT (String 1, String 2, String 3, ...): concatenates strings 1, Strings 2, strings 3, and so on. However, Oracle's CONCAT () allows only two parameters, such as nesting of multiple parameters to be nested using concat, or you can use the | | To Splice!!!

Perform the following sql:1) select name | | ' (' | | | num | | ' kg ' as str from testtable;

2) Select concat (Name, ' (' | | num | | ' kg ') as Str from testtable; The following results can be obtained

4. Intercept string function substr (string, intercept start position, intercept length)

Perform the following sql:select substr (name,0,1) str from testtable; The following results can be obtained

5. Find function INSTR (string,substring,position,ocurrence) Find string position

This function can be used for fuzzy queries and to determine the inclusion relationship:

Example: 1) Select Id,name, num from testtable where InStr (name, ' banana ') >0;

Equivalent to select Id,name, num from testtable where name like '% banana% ';

2) Select Id,name, num from testtable where InStr (' 123, Banana ', name) >0;

Equivalent to select Id,name, num from testtable where name in (' 123, banana ');

Oracle Functions-Merge, replace, splice, intercept, find