Oracle String Functions

1. Replace,translate

--translate character-level substitution--Replace string-level substitutionSelect Replace('abaabbb','AB','C') fromDual--CACBBSelectTranslate'aaabbb','AB','CE') fromDual--ccceee

2. Concat string connection, equivalent to | |

Select concat ('test'_concat ' from dual;  --test_concatSelect'test'| | ' _concat '  from -- Test_concat

3. Upper,lower,initcap specifying a language set: Nls_upper,nls_lower,nls_initcap

Select Upper('aaaaaa') fromDual--aaaaaaSelect Lower('aaaaaa') fromDual--aaaaaaSelectInitcap ('AAAAA') fromDual--AaaaaSelectInitcap ('AAA AAA') fromDual--AAA AAA

4.lpad,rpad string padding, if no padding character is specified, the default is a space

Select lpad ('aa',ten,'0' from -- left padding  00000000aaSelect rpad ('aa',ten,'  0'from-- right fill  aa00000000

5. Ltrim,rtrim,trim (Leading,trailing,both) string trimming, trim only supports single character pruning? Default cut-off spaces

Select LTrim('111111111000123000',' on') fromDual--cut off all 0 and 1 23000 from the left .Select RTrim('00012300011111','012') fromDual----Cut all 0 and 1 000123 from the rightSelectTrim (Leading'0'  from '000123000') fromDual--Cut all 0 from the beginning.123000SelectTrim (Trailing'0'  from '000123000') fromDual--cut off all 0 000123 from the tail.SelectTrim (both'0'  from '000123000') fromDual--Both sides cut all 0 123.

6. substr

--substr (char,start,length) intercepts a char string with length starting from StartSelectSubstr'ABC',0,1) fromDual--returns aSelectSubstr'ABC',1,1) fromDual--Start is 0 and start is 1 effectSelectSubstr'Abcdefgs',-5,3) fromDual--If start is a negative number, the return def is intercepted starting from the countdown start characterSelectSubstr'Abcdefgs',-5) fromDual--if length is omitted, the string to start until the end is truncated to return DEFGS

7. Regexp_substr,regexp_replace

Oracle String Functions