character functions for common functions of Oracle

1. String interception

substr (String, start_position, [length])

Cases:

SelectSubstr'Hello World',1,2) fromDual--The returned result is ' He ' Note: A string of length 2 is intercepted starting from the first character of the stringSelectSubstr'Hello World',0,2) fromDual--The return result is ' He ' Note: Both 0 and 1 are the first characters that represent the start position of the InterceptSelectSubstr'Hello World',-4,3) fromDual--The returned result is ' ORL ' Note: Negative number (-i) means that the intercept starts at the right end of the string, and the first character in the leftSelectSubstr'Hello World',2) fromDual--The return result is ' Ello world ' Note: Omit the number of intercepted characters, then intercept to the end of the stringSelectSubstr'Hello World',9,4) fromDual--The returned result is ' rld ' NOTE: The number of intercepted characters is not valid at this timeSelectSubstr'Hello World',9,-2) fromDual--The returned result is null Note: The number of characters intercepted is less than 1 o'clock and the return value is null

2. Find sub-string location

InStr (string1, string2, [start_position], [nth_appearance])

Cases:

SelectInStr'AABBAACC','AA',2,1) fromDual--returns the result as 5 note: Starting with the string ' AABBAACC ' second character to find the first occurrence of the ' AA ' positionSelectInStr'AABBAACC','AA',2,2) fromDual--The returned result is 0 Note: Starting with the string ' AABBAACC ' second character ' AA ' appears only onceSelectInStr'AABBAACC','AA',-2,2) fromDual--returns the result as 1 note: The second occurrence of the right-to-left ' AA ' positionSelectInStr'AABBAACC','AA',0,1) fromDual--returns the result of 0 Note: The find location should start from 1SelectInStr'AABBAACC','AA',2) fromDual--The returned result is 5 note: The omitted parameter is Nth_appearance, the default is 1SelectInStr'AABBAACC','AA') fromDual--The returned result is 1 Note: The omitted two parameters are 1 by default

3. String substitution

Replace (string, search_string, [replace_string])

Cases:

Select Replace('000123','0','AB') fromDual--return the result as ' ababab123 ' Note: Replace the character ' 0 ' in the string ' 000123 ' with the character ' AB 'Select Replace('000123','0') fromDual--The returned result is ' 123 ' NOTE: Omit the parameter replacement_string, replace the character ' 0 ' with null, i.e. delete all characters ' 0 '

4. String connection

concat (string1, string2) or  ||

Cases:

Select concat (','World' from dual;  -- returns the result as ' Hello world ' Select '  '| | '  World '  from dual;  -- returns the result as ' Hello world '

5. Get the string length

Length (String)

Cases:

Select Length ('from- - Returns a result of 7

6, string to go to space

LTrim (String) RTrim (String) trim (string)

Cases:

Select LTrim('a BC') S1 fromDual--Return the result as ' a BC ' note: Remove the space to the left of the string
Select RTrim('a BC') S1 fromDual--Returns the result as ' a BC ' note: Remove the space to the right of the string
Select Trim('a BC') S1 fromDual--Returns the result as ' a BC ' note: Remove whitespace from the left and right side of the string

7, string to prefix and suffix

Trim ([leading | trailing] Trim_char from string)

Cases:

SelectTrim (Leading'0'  from '0000876700') fromDual--return result as ' 876700 ' Note: Remove prefix
SelectTrim (Trailing'0'  from '0000876700') fromDual--return result is ' 00008767 ' Note: Remove suffix
SelectTrim'0'  from '0000876700') fromDual--return result is ' 8767 ' Note: Remove prefix and suffix

8.

character functions for common functions of Oracle