字符串处理函数

最新推荐文章于 2025-09-09 08:37:13 发布

weixin_30871293

最新推荐文章于 2025-09-09 08:37:13 发布

阅读量63

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/kakaisgood/p/9566147.html

博客介绍了数据库中多个字符串函数，如SUBSTR用于提取字符串部分内容，UPPER将字符串字母转为大写，LOWER转为小写，INITCAP使每个单词首字母大写，REPLACE替换字符串，LENGTH返回字符串长度，INSTR查找子字符串位置等，并给出了各函数的语法。

SELECT
SUBSTR('ABCDEFG',3,4),
SUBSTR('ABCDEFG',3),
SUBSTR('ABCDEFG',-3,3),
UPPER('jinhong'),
lower('X-DEF'),
REPLACE('JACK and JUE','J','BL'),
REPLACE('JACK AND LIFE', 'jack'),
REPLACE('JACK AND LIFE', 'JACK'),
LENGTH('BLAKE'),
INSTR('www.life.com.www', 'www.'),
INSTR('www.life.com.www', 'www',2),
'ONE'||'TWO'

FROM dual;

SUBSTR('ABCDEFG',3,4) SUBSTR('ABCDEFG',3) SUBSTR('ABCDEFG',-3,3) UPPER('JINHONG') LOWER('X-DEF') REPLACE('JACKANDJUE','J','BL') REPLACE('JACKANDLIFE','JACK') REPLACE('JACKANDLIFE','JACK') LENGTH('BLAKE') INSTR('WWW.LIFE.COM.WWW','WWW.') INSTR('WWW.LIFE.COM.WWW','WWW',2) 'ONE'||'TWO'
--------------------- ------------------- ---------------------- ---------------- -------------- ------------------------------ ----------------------------- ----------------------------- --------------- -------------------------------- --------------------------------- ------------
CDEF                  CDEFG               EFG                    JINHONG          x-def          BLACK and BLUE                 JACK AND LIFE                  AND LIFE                                   5                                1                                14 ONETWO

SUBSTR:
Extracts a portion of a string.
Syntax:
SUBSTR(original_string,position[,substring_length])
It returns a portion of original_string, beginning at
If position is negative Oracle counts backward from the end of
The substring_length parameter is optional. If it is omitted, the function returns
UPPER:
Returns the string with all letters uppercase.
Syntax:
UPPER(original_string)
LOWER:
Returns the string with all letters lowercase.
Syntax:
LOWER(original_string)
INITCAP:
Returns the string with the first letter of each word in uppercase, all other letters in lowercase.
Syntax:
INITCAP(original_string)
REPLACE:
Returns the string with every occurrence of search_string
then all occurrences of search_string are removed
Syntax:
REPLACE(original_string,search_string,replacement_string)
LENGTH:
Returns the length of the string.
Syntax:
LENGTH(string)
INSTR:
Returns the position in which one string is found within another string.
Syntax:
INSTR(main_string,substring[,position[,occurrence]]