string types and related functions in Oracle __c language

Reprint please indicate the source: http://blog.csdn.net/anxpp/article/details/51550479, thank you. 1. Overview

This article describes the string type and related functions, based on the current Oracle 12c.

The string is referred to as strings below.

There are two ways to work with Oracle functions:

1. Create new objects based on old objects-they modify the original information, such as changing the case of letters.

2, tell the user about the information, such as a word or a sentence in a few characters.

Subsequent updates will update two other ways of working with the text: Regular expressions in Oracle and Oracle text tools, and so on, where the articles are edited, and the links are added here.

There are two main types of strings in Oracle: Char and VARCHAR2, which exist in a mixed form of letters, punctuation, numbers, and spaces.

The char string is always fixed, and if the value you set is less than the string value of the char column, the space is automatically populated. When the char string is compared, the two sides are filled with spaces and then compared.

The VARCHAR2 data type is a string with a side length (varchar is synonymous with VARCHAR2). 2, string processing functions 2.1. Overview

See table below:

2.2, Connectors | | and concat function

You can connect two column names or constants.

Like what:

SELECT firstname| | LastName from USER;

If the name is good in Chinese, but if it is in English, so the connection will make it difficult to read, so you can add constant "space" in the middle:

SELECT firstname| | ' '|| LastName from USER;

The same effect can be achieved by using the CONCAT function:

SELECT CONCAT (firstname,lastname) from USER;

Results:

However, the CONCAT function conforms to the ANSI SQL standard, so it is suitable for many different databases, | | is Oracle proprietary and is simpler to use. 2.3. Unified format: Rpad and Lpad

Rpad allows a set of characters to be padded to the right of the column, and the filled character can be any character. Lpad is added from the left.

How to use:

Rpad (string,length[, ' Set '])

Lpad (string,length[, ' Set '])

The string here is a string column or constant in the database, length is the size of the fill, and the set is a string to fill. Blank padding is used by default if the contents of the brackets are omitted.

Use of Rpad:

SELECT rpad (lastname,10, ' _ ') as name from users;

Results:

Use of Lpad:

SELECT Lpad (lastname,10) as name from users;

Results:

2.4. Trim: Ltrim,rtrim,trim

LTrim and RTrim remove unwanted characters from the left or right side of the string.

How to use:

RTRIM (string[, ' Set '])

LTRIM (string[, ' Set '])

If you do not set the value you want to delete, delete the space by default.

Let's use the example to explain that there is a table for the title, and the value of the list is as follows:

SELECT name from book;

This is because the input data is not the same person, resulting in inconsistent data format, first of all, let's try the LTrim function:

SELECT LTRIM (name, ' ") from book;

You can see that the left is unified, and the next is right, we can easily combine LTrim and RTrim:

SELECT RTRIM (LTRIM (name, ' '), ' ") from book;

As you can see, the results of the query have been unified. You can delete more than one character at a time, just set the string you want to delete.

We are looking at an example where the contents of a field are: "The Easy Way":

INSERT into book (id,name) VALUES (6, ' The Easy Way ');

We tried to delete the previous and extra spaces, so we executed the following script:

Select LTrim (Name, ' the ') from book where id = 6;

Results: Asy Way

The discovery is not what we want, thinking that when LTrim deletes the string we specify, it is not the string, but the characters in it, which exits only if the first time a character is found that does not belong to the string we specified. The principle of RTrim and trim is the same. In order to achieve the above goal, we can do this:

Select LTrim (LTrim (name, ' the '), ' from book where id = 6;

This will output the results we want. But there are simpler ways to use the InStr, SUBTR, and decode functions, which are described later in this article.

When the characters we want to delete on both sides are the same, you can use the TRIM function, and if you want to get a simple title, you can use the trim (' "' from Name ') if the title in the previous example could be in the form of" Thought in Java "(both sides are single quotes):

Select Trim (' ' from Name ') from the book where id=7;

Note that only a single character can be removed from the trim, and the shape of trim (' from name ') is incorrect.

If you want to remove from a section of a string, you can use the leading and trailing words, and then they function in LTrim and RTrim are the same:

Select trim (Leading ' ' from Name ') from the book where id=7;

Result: "In Java" 2.5, uppercase and lowercase conversions: LOWER, Upper, and Initcap

Lower converts any letter of a string or column to lowercase.