Shulou(Shulou.com)06/01 Report--

This paper mainly summarizes the role and use of the following character functions:

function stating ASCII returns the decimal value of the corresponding character CHR gives the decimal return character CONCAT concatenates two strings, and|| Same INITCAT Change the first letter of a string to upper case INSTR Find the position of a string INSTRB Find the position and number of bytes of a string LENGTH Give the length of the string in characters LENGTHB Give the length of the string in bytes LOWER Convert the string to lower case LPAD Fill the left side of the character with the specified character LTRIM Cut the specified character on the left RPAD Fill the right side of the character RTRIM Cut the specified character on the right REPLACE Perform string search and replace SUBSTR Take character SUBSTRB takes a substring of a string (in bytes) SOUNDEX returns a homophone string TRANSLATE performs string search and replacement TRIM cuts out the preceding or following string UPPER capitalizes the string NVL replaces null with a value

1、ASCII()

Is a string that returns the decimal number corresponding to the specified character.

SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

A a ZERO SPACE

---------- ---------- ---------- ----------

65 97 48 32

2、CHR(n])

Given an integer, return the corresponding character. For example:

SQL> select chr(65) chr65 from dual;

C

-

A

3、CONCAT(,)

Returns the combined value of string c1 and string c2. For example:

SQL> select concat('010-','8801 8159') from dual;

CONCAT('01

-----------------

010-8801 8159

4、INITCAP()

Returns the string c1 with the first letter capitalized. For example:

SQL> select initcap('simth') upp from dual;

UPP

-----

Simth

5、INSTR(,[,[,] ] )

Searches a string for the specified character and returns the location where the specified character was found.

C1: Searched string

C2: String you want to search

I: Start position of search, default is 1

J: Position where it appears, default is 1.

SQL> SELECT INSTR ('Oracle Training', 'ra', 1, 2) "Instring" FROM DUAL;

Instring

----------

9

6、INSTRB(,[,[,] ] )

Same as INSTR except bytes returned,

7、LENGTH( )

Returns the length of string c.

SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from nchar_tst;

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

------ ------------ ---------------- ------------ ---------- ----------------

Zhang Wuyi 3 Beijing City Haidian District 6 9999.99 8

8、LENGTHB( )

Returns the number of bytes of a string in bytes.

SQL> select name,lengthb(name),length(name) from nchar_tst;

NAME LENGTHB(NAME) LENGTH(NAME)

------ ------------- ------------

Zhang 51 6 3

9、LOWER ( )

Returns a string and changes all characters to lowercase.

SQL> select lower('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

aabbccdd

10、UPPER( )

As opposed to LOWER, capitalize the given string. For example:

SQL> select upper('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

AABBCCDD

11. RPAD and LPAD

RPAD(string，Length[，'set'])

LPAD(string，Length[，'set'])

RPAD is completed with specific characters on the right side of the column;

LPAD is filled in on the left side of the column with specific characters.

Example 1:

SQL>select RPAD(City，35，'. ')，temperature from weather;

RPAD(City，35，'. ') temperature

-------------------------- ----------------

CLEVELAND...... 85

LOS ANGELES.. 81

.........................

(Not enough 35 characters.) 'Complete)

12. LTRIM(left truncation)RTRIM (right truncation)

LTRIM (string [,'set'])

Left TRIM Delete any set characters that appear on the left.

RTRIM (string [,'set'])

Right TRIM Delete any set character that appears to the right.

Example 1:

SELECT RTRIM ('Mother Theresa, The', 'The') FROM DUAL;

RTRIM ('M

---------------

Mother Theresa,

SUBSTR Substr(string, m[, n]) intercepts string function

substr("ABCDEFG", 0); //returns: ABCDEFG, intercepts all characters

substr("ABCDEFG", 2); //returns: CDEFG, intercepts all characters starting with C

substr("ABCDEFG", 0, 3); //returns ABC, truncating 3 characters starting from A

substr("ABCDEFG", 0, 100); //Return: ABCDEFG, 100 Although it exceeds the maximum length of the preprocessed string, it does not affect the returned result. The system returns the maximum number of preprocessed strings.

substr("ABCDEFG", -3); //Returns: EFG, note parameter-3, negative value indicates that the string arrangement position remains unchanged from the end.

14、SUBSTRB(string，m[，n])

For a string (or field), start with m bytes, take n bytes in succession and return the result, if there is no n, take it all the way to the end.

15、REPLACE ('string' [,'string_in','string_out'])

String: The string or variable you want to replace.

String_in: The string to be replaced.

String_out: String to replace.

SQL> select replace ('111222333444','222','888') from dual;

REPLACE('111

--------------------

111888333444

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.