Oracl commonly used string functions

2011-03-30  来源:本站原创  分类:Database  人气:94 

Character variables store the maximum value:

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 2GB

Clob type 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 a string first letter of each word 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 and word of the other letters lowercase 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 is 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 the string all the letters are capitalized in 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: the string of all the sub-string search_str replaced with optional replace_str, if not specified replace_str, all of the substring in string

search_str will be deleted. REPLACE is provided by the TRANSLATE function of 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 the 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 the string

Longer. RPAD and LPAD behavior is similar, except that it is on the right instead of 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 letter from the beginning b-character string of a substring if a is 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 right to left starting at the end of string calculated 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 each character replaced to_str the corresponding character in subsequent string. TRANSLATE function is REPLACE provides a superset.

If from_str longer than to_str, then from_str to_str in suits and not in the character string will be removed, 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, 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. 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 be 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 beginning 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 type value is padded with spaces 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.

select substr (t.cm_node, 0, instr (t.cm_node ,'/',- 1)) from cm_node t;

相关文章
  • Oracl commonly used string functions 2011-03-30

    Character variables store the maximum value: 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 2GB Clob type 4GB 1, CHR Syntax: chr (x) Function:

  • Commonly used string functions: 2011-05-05

    Commonly used string functions: SQL> select instr ('Hello World!', 'O', 1,1), instr ('Hello World!', 'O', 1,2), instr ('Hello World!', 'O', 6 , 2) from dual; INSTR ('HELLOWORLD!', 'O', 1,1) INSTR ('HELLOWORLD!', 'O', 1,2) INSTR ('HELLOWORLD!', 'O', 6

  • mysql commonly used string functions 2011-04-25

    Keywords: mysql terminal; string functions Reference URL: http://blog.sina.com.cn/s/blog_45b088420100bst4.html Reference URL: http://dev.mysql.com/doc/refman/5.1/zh/functions.html # String-functions MySql string functions ASCII(str) Returns a string

  • js of some commonly used string functions 2009-05-10

    Function: split () Function: the use of a designated separator to split a string into an array of storage Examples: str = "jpg | bmp | gif | ico | png"; arr = theString.split ("|"); / / arr is a character value contains "jpg"

  • C language implementation of commonly used string functions 2010-10-07

    The last day of vacation, and continue. // String length int strlen(char *s) { int i=; while(*s++) i++; return i; } / / string copy void strcpy(char *s,char *t) { while(*s++=*t++) ; } / / String comparison int strcmp(char *s,char *t) { while((*s++)==

  • SQL String Functions 2011-01-17

    String function of binary data, strings and expressions to perform different operations. Such a function acting on CHAR, VARCHAR, BINARY, and VARBINARY data types, and can be implicitly converted to a CHAR or VARCHAR data type. SELECT statement in th

  • Freemarker commonly used built-in functions and usage of 2009-02-28

    A, Sequence of the built-in functions 1. Sequence? First return sequence of the first value. 2. Sequence? Last returns the last value of sequence. 3. Sequence? Reverse the existing order of the sequence reversed, that is, reverse sort 4. Sequence? Si

  • php string functions 2010-03-01

    A variety of PHP string functions summary PHP language is a string function is easier to understand knowledge. Today we are all summed up for the nearly 12 species of PHP string functions, in the hope they need to help a friend, a friend of PHP knowl

  • Transfer: concat and concat_ws () difference and a few useful string functions MySQL 2010-03-15

    Keywords: mysql useful string functions 1, concat () function 1.1 MySQL's concat function can be connected to one or more strings, such as mysql> select concat ('10 '); +--------------+ | Concat ('10 ') | +--------------+ | 10 | +--------------+ 1 ro

  • Does not appear to delete all the replies show replies show star returns to score back ORACLE PL / SQL string functions. Mathematical functions. Date function 2010-08-04

    ORACLE PL / SQL string functions, math functions, date functions - String functions] [ - String interception substr (field name, the starting point, number) select Name, substr (Name, 2,4), substr (Name, 0,3), substr (Name, -2,3), substr (Name, -2,1)

  • mysql string functions: string interception 2010-08-27

    MySQL string functions: string interception MySQL string interception function: left (), right (), substring (), substring_index (). There are mid (), substr (). One, mid (), substr () is equivalent to substring () function, substring () function is

  • mysql string functions <2> 2010-09-15

    SUBSTRING (str, pos) SUBSTRING (str FROM pos) String str from the start position pos return a substring. mysql> select SUBSTRING ('Quadratically', 5); -> 'Ratically' mysql> select SUBSTRING ('foobarbar' FROM 4); -> 'Barbar' This function is mu

  • (Transfer) sql server string functions Detailed Classification 2010-10-28

    Transfer from http://database.51cto.com/art/201009/225705.htm The following string functions sql server will be classified an explanation, I believe that these string functions sql server can use the methods you learn SQL database can play a helpful

  • PHP string functions summary 2010-11-05

    PHP string functions summary ================================== ceil, floor, round, /,% float ceil (float $ value) to go the whole (further) cout (1.8) => 2 cout (-1.8) => 2-1 float floor (float $ value) to go the whole (down) floor (1.8) => 1 fl

  • List of string functions sqlserver 2010-12-08

    We are here SQL Server string functions listed in different categories for easy access and memory, I believe we have advanced in other programming basics, from literally everyone knows the meaning of these functions, do not make too much of these fun

  • Oracle function Daquan (string functions, math functions, date functions, logical functions, other functions) 2010-12-09

    Oracle function Daquan (string functions, math functions, date functions, logical functions, other functions) Keywords: Oracle Functions String Functions Date Functions Mathematical Functions logical function SQL functions in a single record 1.ASCII

  • (Transfer) JavaScript string functions Daquan 2010-12-16

    JS own function concat Two or more combinations of text characters and returns a new string. var a = "hello"; var b = ", world"; var c = a.concat (b); alert (c); / / C = "hello, world" indexOf Returns a substring in the strin

  • MySQL built-in function table, including string functions. Mathematical functions, and date and time functions 2011-01-10

    String Functions ASCII (str) Returns the string str for the first character ASCII value (str is an empty string returns 0) mysql> select ASCII ('2 '); -> 50 mysql> select ASCII (2); -> 50 mysql> select ASCII ('dete'); -> 100 ORD (str) If

  • SQLServer2008 list of string functions 2011-05-31

    / * String functions (PS. indexes are calculated from 1) * / / * The specified character (or string) A, string B, the starting index. A in B to obtain the index value. * / select Charindex ('d', 'abcdefg', 0) - 4 select Charindex ('bc', 'abcdefg', 0)

  • . NET string functions when the report design 2011-07-18

    String functions in any application have been widely used, is no exception reporting tools, reporting tools will provide the general part of the string handling functions, such as access to substring, removing spaces, character conversion (turn unico