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

This article mainly introduces the "Oracle commonly used character function example tutorial". In the daily operation, I believe that many people have doubts on the common character function example tutorial of Oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Oracle common character function example tutorial". Next, please follow the editor to study!

1. Experimental preparatory action

1) create experimental table T

Sec@ora10g > create table t (first_name varchar2 (15), family_name varchar2 (15), alias_name varchar2 (15), mark varchar2 (15), ascii_num int

Table created.

2) initialize a piece of data

Sec@ora10g > insert into t values ('Secooler', 'HOU',' sec', 'Houses, 72)

1 row created.

Sec@ora10g > commit

Commit complete.

Sec@ora10g > select * from t

FIRST_NAME FAMILY_NAME ALIAS_NAME MARK ASCII_NUM

--

Secooler HOU sec H 72

2.CHR function

The CHR function can get the character corresponding to the numeric value. Because we use the ASCII character set, the character corresponding to the number "72" is "H".

Sec@ora10g > select chr (ASCII_NUM) chr from t

CHR

-

H

Have to mention the ASCII function, this function can be understood as the "inverse function" of the CHR function, through the ASCII function can get the ASCII character encoding of the character.

Sec@ora10g > select ascii ('H') ascii from t

ASCII

-

seventy-two

Sec@ora10g > select ascii (mark) ascii from t

ASCII

-

seventy-two

3.CONCAT function

The CONCAT function is similar to the "| |" symbol in that it concatenates two characters.

Sec@ora10g > select concat (FIRST_NAME,FAMILY_NAME) "My Name is" from t

My Name is

-

Secooler HOU

Use "| |" to achieve the same effect

Sec@ora10g > select FIRST_NAME | | FAMILY_NAME "My Name is" from t

My Name is

-

Secooler HOU

4.INITCAP function

The INITCAP function literally knows what it does, converting the first letter of a string to uppercase and the rest to lowercase. Regardless of whether the initial content of the string is uppercase or lowercase, it is converted to a uniform form.

Sec@ora10g > select initcap (FIRST_NAME) initcap, initcap (FAMILY_NAME) initcap from t

INITCAP INITCAP

--

Secooler Hou

5.LOWER and UPPER functions

The LOWER and UPPER functions are similar to the INITCAP function, except that the LOWER function here converts all strings to lowercase letters, and the UPPER function converts all strings to uppercase letters.

1) demonstration of LOWER function

Sec@ora10g > select lower (FIRST_NAME) lower, lower (FAMILY_NAME) lower from t

LOWER LOWER

--

Secooler hou

2) demonstration of UPPER function

Sec@ora10g > select upper (FIRST_NAME) upper, upper (FAMILY_NAME) upper from t

UPPER UPPER

--

SECOOLER HOU

6.LPAD and RPAD functions

The LPAD and RPAD functions accomplish the purpose of filling a string with specific characters to a specified length.

1) fill the left side of the FAMILY_NAME with the "$" symbol so that the length of the entire string is 10.

Sec@ora10g > select lpad (FAMILY_NAME,10,'$') lpad from t

LPAD

-

$HOU

2) fill in the "$" symbol to the right of the FAMILY_NAME so that the length of the entire string is 10.

Sec@ora10g > select rpad (FAMILY_NAME,10,'$') rpad from t

RPAD

-

HOU$

3) mixed use, add three "#" symbols to the left of the FAMILY_NAME and four "$" symbols after it.

Sec@ora10g > select rpad (lpad (FAMILY_NAME,6,'#'), 10 LPAD and RPAD from t)

LPAD and RPAD

-

# HOU$$$$

4) if you do not specify a filled character, it will be filled with spaces by default

Sec@ora10g > select lpad (FAMILY_NAME,10) lpad from t

LPAD

-

HOU

7.LTRIM and RTRIM functions

The purpose of the LPAD and RPAD functions is to populate, and the purpose of the LTRM and RTRIM functions is to delete the corresponding content.

1) remove the "S" letter that appears on the left side of FIRST_NAME

Here, in order to verify the deletion of the "all" left "S" letters, we initialize a content that contains multiple S in front of it.

Sec@ora10g > insert into t (FIRST_NAME) values ('SSSecooler')

1 row created.

Sec@ora10g > select FIRST_NAME, ltrim (FIRST_NAME,'S') ltrim from t

FIRST_NAME LTRIM

--

Secooler ecooler

SSSecooler ecooler

As you can see, the "S" that appears to the left of the FIRST_NAME field has all been deleted.

2) remove the "U" letter that appears on the right side of FAMILY_NAME

Sec@ora10g > select FAMILY_NAME, rtrim (FAMILY_NAME,'U') rtrim from t

FAMILY_NAME RTRIM

--

HOU HO

In the same way, the RTRIM function accomplishes the purpose of deleting "all" characters. There is no more demonstration here.

3) if the LTRIM and RTRIM functions do not use the second argument, the function is to remove the spaces on the left side (LTRIM) or the right side (RTRIM) of the string, which is a common basic function.

First introduce these seven sets of character functions, to be continued ing.

Good luck.

Secooler

At this point, on the "Oracle common character function example tutorial" on the end of the study, I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.