Oracle stored procedure summary String handling related functions

2011-08-12  来源:本站原创  分类:Database  人气:115 

Characters function - returns the character value

These functions are all family to receive a parameter of type character (CHR excluded) and returns the character value.

Except as noted in addition, most of these functions returns a VARCHAR2 value.

The return type of character limitations and the limitations of basic database types are the same.

The maximum character variable storage:

VARCHAR2 values ​​are limited to 2000 characters (ORACLE 8 for 4000 characters)

CHAR values ​​are limited to 255 characters (in ORACLE8 is 2000)

long type to 2GB

Clob type of 4GB

1, CHR

Syntax: chr (x)

Function: Returns the database character set and X has a value equivalent to the character. CHR and ASCII is a pair of inverse functions. After CHR converted ASCII characters and then convert after the original word has been

Character.

Use position: the process of statements and SQL statements.

2, CONCAT

Syntax: CONCAT (string1, string2)

Function: Returns string1, and later connected string2.

Use position: the process of statements and SQL statements.

3, INITCAP

Syntax: INITCAP (string)

Function: Returns the string the first letter of each word in uppercase and lowercase letters of other words in the string. The word is used. Alphanumeric characters to a space or separated. Not the letter

Character does not change.

Use position: the process of statements and SQL statements.

4, LTRIM

Syntax: LTRIM (string1, string2)

Function: Returns the date removed from the left in the character of string2 in string1. String2 is defaulted to a single space. The database will scan string1, starting from the left. When

String2 is not the first encounter a character, the result is returned. LTRIM and RTRIM behavior is very similar.

Use position: the process of statements and SQL statements.

5, NLS_INITCAP

Syntax: NLS_INITCAP (string [, nlsparams])

Function: Returns a string first letter of each word uppercase and lowercase letters of other words in the string, nlsparams

Unlike the session specified a different default sort sequence. If not specified, the function and INITCAP same. Nlsparams can use the form:

'NLS_SORT = sort'

This sort develop a language sort sequence.

Use position: the process of statements and SQL statements.

6, NLS_LOWER

Syntax: NLS_LOWER (string [, nlsparams])

Function: Returns string with all letters are lowercase string. Not the letter characters remain unchanged.

Nlsparams parameters form and use and NLS_INITCAP in nlsparams parameters are the same. If nlsparams not included, then processed and made NLS_LOWER

LOWER same.

Use position; procedural statements and SQL statements.

7, NLS_UPPER

Syntax: nls_upper (string [, nlsparams])

Function: Returns a string in all uppercase letters are the form of string. Not the letter characters remain unchanged. nlsparams parameters form and use and NLS_INITCAP the same. If

Not set parameters, functions and UPPER NLS_UPPER same.

Use position: the process of statements and SQL statements.

8, REPLACE

Syntax: REPLACE (string, search_str [, replace_str])

Function: string substring of all with optional replace_str search_str replace, if not specified replace_str, all of the substring in string

search_str will be deleted. REPLACE the TRANSLATE function provided by a subset.

Use position: the process of statements and SQL statements.

9, RPAD

Syntax: RPAD (string1, x [, string2])

Function: Returns the length in the X position of the character into a character of string2 in string1. If the length of string2 less than X characters, in accordance with the need for replication. If string2

More than X characters, then only the characters in string1 in front of X is used. If not specified, string2, then use the space to fill. X is the display length can be compared with the actual length of string

Longer. RPAD and LPAD behavior is similar, except that it is on the right rather than left to fill.

Use position: the process of statements and SQL statements.

10, RTRIM
Syntax: RTRIM (string1, [, string2])

Function: Returns the date removed from the right appears the characters that appear in string1 string2. String2 is defaulted to a single space. The database will scan string1, starting from the right when the case

Not string2 to the first character, the result is returned and the RTRIM and LTRIM behavior is very similar.

Use position: the process of statements and SQL statements.

11, SOUNDEX

Syntax: SOUNDEX (string)

Function: Returns the string representation of the sound, which compare two spellings for similar sounds in different words, but in terms of help.

Use position: the process of statements and SQL statements.

12, SUBSTR

Syntax: SUBSTR (string, a [, b])

Function: Returns the value of a start from the letter b for the long string of characters in a substring. If A is a 0, then it is considered a character from the first, if is positive, returns the character is left

Calculated to the right side, if b is negative, then the return of characters from string beginning at the end of calculated right to left if b does not exist, then it will default character set for the entire

String if b is less than 1, then returns NULL. If a or b using the floating-point, then the value will be before the first treatment, but as an integer.

Use position: the process of statements and SQL statements.

13, TRANSLATE

Syntax: TRANSLATE (string, from_str, to_str)

Function: Returns to the emergence of from_str replace each character in the corresponding character to_str after the string. TRANSLATE function is REPLACE provide a superset.

If from_str than to_str long, then from_str to_str in suits and not in character from the string is deleted, because they do not replace the corresponding character. To_str can not be empty

. Oracle the empty string that is NULL, and if the TRANSLATE in any parameter is NULL, then the result is NULL.

Use position: the process of statements and SQL statements.

14, UPPER

Syntax: UPPER (string)

Function: Returns an uppercase string. Alphabetic characters is not the same if the string data type is CHAR, then the result is CHAR type. If the string is a VARCHAR2, then

The result is of type VARCHAR2.

Use position: the process of statements and SQL statements.

Characters function - returns the number

These functions accept character arguments back to the numerical results. Arguments can be CHAR or VARCHAR2 type, although many results are actually lower integer value, but the results are simple returns NUMBER

Type, does not define the scope of any precision or scale.

16, ASCII

Syntax: ASCII (string)

Function: Database character set to return a byte string of the first decimal representation. Please note that the function is still known as ASCII. Although many of the character set is not 7 ASCII.CHR and ASCII are mutually

The opposite function. CHR has been given the response character encoding characters. ASCII characters to get a given character encoding.

Use position: the process of statements and SQL statements.

17, INSTR

Syntax: INSTR (string1, string2 [a, b])

Function: get in the position of string1 contains string2. String1 beginning from the left to check, the starting position as a, if A is a negative number, then string1 is to start from the right

Scan. The first b second occurrence will be returned. A and b are set to 1 by default, which will return the first occurrence in string1 string2 position. If string2 in a and b is not under the provisions of

Found, then return 0. Position is calculated relative to the starting position of string1, regardless of the values ​​of a and b is the number.

Use position: the process of statements and SQL statements.

18, INSTRB

Syntax: INSTRB (string1, string2 [a, [b]])

Function: and INSTR the same, but the operation parameters of the character used for the location of the byte.

Use position: the process of statements and SQL statements.

19, LENGTH

Syntax: LENGTH (string)

Function: Returns the string the length of the byte unit. CHAR values ​​are padded with spaces of type, if the string from the data types CHAR, it's the end of the spaces have been calculated to the middle of the string length.

If the string is NULL, the result returned is NULL, not 0.

Use position: the process of statements and SQL statements.

20, LENGTHB

Syntax: LENGTHB (string)

Function: Returns the string in bytes the length of the single-byte character sets LENGTHB and LENGTH is the same.

Use position: the process of statements and SQL statements.

21, NLSSORT

Syntax: NLSSORT (string [, nlsparams])

Function: get a string of bytes used to sort the string, all values ​​are converted to byte strings, so that different databases to maintain consistency between. Nlsparams role and

NLS_INITCAP the same. If you omit the session using the default sort.

Use position: the process of statements and SQL statements.

相关文章
  • Oracle stored procedure summary String handling related functions 2011-08-12

    Characters function - returns the character value These functions are all family to receive a parameter of type character (CHR excluded) and returns the character value. Except as noted in addition, most of these functions returns a VARCHAR2 value. T

  • Oracle stored procedure summary 2010-07-21

    6, the array First of all, a concept clearly: Oracle in the concept of this is not an array, the array is actually a table (Table), each array element is a record in the table. Array, the user can use Oracle has defined array type, or according to th

  • oracle stored procedure summary (a) 2010-10-15

    1, create a stored procedure create or replace procedure test (var_name_1 in type, var_name_2 out type) as - Declare a variable (variable type variable name) begin - Body of stored procedure execution end test; Print out the input of time information

  • Oracle stored procedure summary (I. Basic applications) 2010-12-22

    1, create a stored procedure create or replace procedure test (var_name_1 in type, var_name_2 out type) as - Declare a variable (variable type variable name) begin - Body of stored procedure execution end test; Print out the input of time information

  • Oracle stored procedure summary (basic application) 2011-09-23

    1, create a stored procedure create or replace procedure test (var_name_1 in type, var_name_2 out type) as - Declare a variable (variable type variable name) begin - Body of stored procedure execution end test; Print out the input of time information

  • Oracle Stored Procedure Summary (II. String handling related functions) 2010-12-22

    Projects involving the handling of strings stored procedures, so will find information on the Internet a summary of the information to make a mosaic-style summary. The following information from the Internet, posted a save to be their own later use,

  • Oracle stored procedure summary (two. String processing related functions) 2010-12-22

    Project has involved the handling of the string stored procedures, so will find information on the Internet summary, make a mosaic-style summary of the information. The following information comes from the Internet, posted a save to be after their us

  • oracle stored procedure summary (II) 2010-10-15

    Characters function - returns the character value These functions are all family to receive a parameter of type character (CHR excluded) and returns the character value. Except as noted in addition, most of these functions returns a VARCHAR2 value. T

  • Calling oracle stored procedure with java Summary 2009-07-18

    1: no return value stored procedure Stored procedure as follows: CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called inside the java

  • Call oracle stored procedure with java Summary (transfer) 2010-03-07

    A: No return value stored procedure Stored procedure is: CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called in the java in the followi

  • Call oracle stored procedure with java Summary 2010-06-09

    1, what is stored procedure. Stored procedure is a section of the database server-side program, it has two types. A kind of similar to the SELECT query used to retrieve the data, the data can be retrieved in the form of a data set returned to the cus

  • JAVA ORACLE stored procedure call summary 2010-06-13

    1, no return value stored procedure Stored procedure is: (the process is to add the department dept) create or replace procedure adddept (deptno number, dname varchar2, loc varchar2) as begin if deptno is not null then insert into dept values (deptno

  • java call oracle stored procedure sample summary 2010-09-18

    oracle java stored procedure is called an example of a conclusion: no return value stored procedure Stored procedure is: create or replace procedure adddept (colno number, colname varchar2, colc varchar2) as begin insert into table values (colno, col

  • Spring calls the results of a small set of Oracle stored procedure 2010-03-29

    oracle advanced features for the congregation is always different (I strongly dislike this point, if you use his product is necessary for this product-specific programming, which is like I have never been bullish weblogic platform reasons), keep larg

  • Oracle stored procedure 2010-03-30

    1. Overview Oracle stored procedure development key points: • Use Notepad text editor, using Oracle PL / SQL programming language to write a stored procedure; • In the Oracle database to create a stored procedure; • In the Oracle database using SQL *

  • mysql 5.0 stored procedure summary 2010-02-09

    mysql 5.0 stored procedure learning summary 1. Create a stored procedure 1. Basic syntax: create procedure sp_name () begin ... ... ... end 2. Parameter 2. Call a stored procedure 1. Basic syntax: call sp_name () Note: The stored procedure name must

  • ORACLE stored procedure in the asynchronous call 2010-04-09

    ORACLE stored procedure in the asynchronous call Analysis: Wang Hui Source: SAN Published: 2003.05.26 1. About the Author Wang Hui work since 1994, has served as teacher, database administrators, the main programmer, project manager for a company as

  • spring calling Oracle stored procedure and returns a complete example of the result set 2010-06-12

    This is a summary of previous call to Oracle stored procedure using the spring, and with the cursor result set to return a complete example and I hope you can help. 1. Create a table: Java code create table TEST_USERS ( USER_ID VARCHAR2 (10) not null

  • JAVA and ORACLE stored procedure 2010-07-19

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • oracle stored procedure and function calls return a result set 2010-07-28

    In program development, the common to the return result sets stored process, which in mysql and sql server li better deal, directly back query results Jiu may Le, But out in the oracle inside to out, the more of a step, for the unfamiliar brothers al