The character function of Oracle single-line function

Source: Internet
Author: User
Tags rtrim truncated

This paper summarizes the function of the following character function and how to use it: functionDescription ASCIIReturns the decimal value of the corresponding character CHRgives the decimal return character CONCATStitching two strings, with | | Same InitcatCapitalizes the first letter of a string INSTRFind out where a string is located INSTRBFind the position and number of bytes of a string LENGTHThe length of the string given as a character LENGTHBThe length of the string given in bytes LOWERConvert a string to lowercase lpadFills the left of the character with the specified character LTRIMTrims the specified characters on the left RpadFills the right side of the character with the specified character RTRIMTrim the specified character to the right of REPLACEPerform string search and replace SUBSTRTakes a substring of a string substrbTakes a substring of a string (in bytes) SOUNDEXReturns an TRANSLATE stringPerform string search and replace TRIMCrop out the front or back string UPPERConverts a string into uppercase NVLReplaces the null value with a value of 1, ASCII (<xxx>) <xxx> 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----------------------------------------    &NB Sp       &NBSP;2, CHR (n]) gives an integer that returns the corresponding character. such as: sql> select Chr (chr65) from dual;  c  - a  3, CONCAT (<c1>,<c2>) returns the string C1 the merged value with the string C2. For example: sql> select Concat (' 010-', ' 8801 8159 ') from dual; CONCAT ('-----------------010-8801 8159  4, Initcap (<c1>) returns a string C1 and the first letter becomes uppercase. For example: sql> select Initcap (' simth ') Upp from dual; UPP-----simth  5, INSTR (&LT;C1&GT;,&LT;C2&GT;[,&LT;I&GT;[,&LT;J&GT;]) searches a string for the specified character and returns the location of the specified character. C1: searched string C2: The string I want to search for: The starting position of the search, the default is 1 J: where it appears, the default is 1. Sql> Select INSTR (' Oracle Training ', ' Ra ', 1, 2) ' instring ' from DUAL;  instring----------        9  6, INSTRB (<c1>,<c2>[,<I>[,<J>]) In addition to the bytes returned, the length of string C is returned as InStr,  7, Length (<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))------------------------------------- -----------------------------------51     &NBSP;3         Beijing Haidian       &NB Sp 6         99999.99    8  8, LENGTHB (<c>) 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)-------------------------------51             &NB Sp;6            3  9, LOWER (<c>) returns a string and turns all characters to lowercase. Sql> Select lower (' AABBCCDD ') AABBCCDD from dual; AABBCCDD--------AABBCCDD  10, UPPER (<c>) and LOWER instead, the string is given uppercase. such as: sql> Select Upper (' AABBCCDD ') AABBCCDD from dual; AABBCCDD--------AABBCCDD &Nbsp;11, Rpad and Lpad rpad (string,length[, ' Set ')) Lpad (string,length[, ' set ')       Rpad with specific characters on the right side of the column;       Lpad the left side of the column with specific characters. Example 1:sql>select   Rpad (city,35, '. '), temperature    from weather;         Rpad (city,35, '. ')            temperature       &N Bsp ------------------------------------------           cleveland ...                    85            los ANGELES.                     .....        . ... ...         (i.e. not enough 35 characters for '. ') Padded)  12, LTRIM (left truncated) RTRIM (right truncated) LTRIM (string [, ' Set ']) left TRIM (truncated) Delete any set character that appears to the left. RTRIM (string [, ' Set ']) right TRIM (left truncated) deletes any set character that appears to the right. Example 1:select RTRIM (' mother Theresa, the ', ' the ')  from DUAL; RTRIM (' M---------------mother Theresa,  13, SUBSTR SUBSTR (String,m[,n]) intercept the string function SUBSTR ("ABCDEFG", 0); return: ABCDEFG, intercept all characters substr ("ABCDEFG", 2); return: CDEFG, intercept all characters after starting from C substr ("ABCDEFG", 0, 3); return: ABC, intercept starting from a 3 characters substr ("ABCDEFG", 0, 100); Return: abcdefg,100 The maximum number of preprocessed strings is returned, although it exceeds the length of the preprocessed string, but does not affect the return result. substr ("ABCDEFG",-3); Return: EFG, note parameter-3, a negative value indicates that the string is not changed from the beginning of the tail.  14, SUBSTRB (String,m[,n]) pairs the string (or field), starting with the M-byte, fetching N bytes consecutively and returning the result, if not referring to N, is taken to the tail. &NBSP;15, replace (' string ' [, ' string_in ', ' string_out ']) string: the strings or variables that you want to be replaced. String_in: the string being replaced. String_out: To replace the string. Sql> Select  replace (' 111222333444 ', ' 222 ', ' 888 ') from dual; REPLACE (' 111--------------------111888333444

For more videos and learning articles, please follow me:

The character function of Oracle single-line function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.