Oracle (11) string function, oracle string

Oracle (11) string function, oracle string

I. String Functions

LENGTH (char1, char2)

Select length ('abc def gh') FROM dual; -- get the LENGTH of the string, containing spaces. Result: 10

 

CONCAT (char1, char2)

Select concat ('abc', 'def ') FROM dual; -- concatenates two strings and returns: 'abcdef' SELECT 'abc' | 'def' FROM dual; -- concatenate two strings: 'abcdef'

 

INITCAP (CHAR ):　

Select initcap ('hello') FROM dual; -- converts the initial character to uppercase and returns "hello ".

 

LOWER (CHAR ):

Select lower ('Hello World') FROM dual; -- converts the string to lowercase and returns "hello world ".

 

UPPER (CHAR ):

Select upper ('Hello World') FROM dual; -- converts the string to uppercase and returns "hello world ".

 

LTRIM (char, set ):

Select ltrim ('Hello World') FROM dual; -- remove the space on the left side of the string: 'Hello world'

 

RTRIM (char, set ):

Select rtrim ('Hello World') FROM dual; -- removes the space on the right of the string and returns 'Hello world'

 

TRANSLATE (char, from, ):

SELECT Translate ('abcdefabcdef ', 'abc', '000000') FROM dual; -- if the replacement character is continuous, replace abc with 123. Result: '123def123def 'SELECT Translate ('abcdefabcdef', 'ace', '000000') FROM dual; -- if the character is not continuous, replace the result with a letter: '1b2d3f1b2d3f'

 

REPLACE (char, search_str, replace_str)　

Select replace ('abcdefabcdef ', 'abc', '000000') FROM dual; -- REPLACE string abc with 123 result: 123def123def

 

INSTR (char, substr [, pos])

SELECT Instr ('abcdefabcdef ', 'def') from dual; -- query the position of def in the string. The result starts from 1 and is 4.
SELECT Instr ('abcdefabcdef ', 'def', 5) from dual; -- query the position of def in the string starting from the specified position. The result starting from 1 is: 10 select instr ('abcedfghedfghijk ', 'def', 1, 2) from dual; -- query the second position of edf in the string. The result starting from 1 is: 9.

 

 

SUBSTR (char, pos, len)

SELECT Substr ('abcdefgh', 3) FROM dual; -- truncates a string starting FROM the second BIT and ends with the result: 'cdefgh' SELECT Substr ('abcdefgh', 3rd) FROM dual; -- the result of intercepting two strings starting with the 3rd bits: 'cd'