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

Source: Internet
Author: User

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

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.