SQL-字符函数

发表于 | 分类于

字符函数

大小写转换函数

函数 结果 说明
LOWER(‘SQL Course’) sql course 字符串转换为小写
UPPER(‘SQL Course’) SQL COURSE 字符串转换为大写
INITCAP(‘SQL Course’) Sql Course 字符串首字母大写，其它小写 
1
2
3
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';

字符处理函数

函数 结果 说明
CONCAT(‘Hello’, ‘World’) HelloWorld 字符串连接
SUBSTR(‘HelloWorld’,1,5) Hello 字符串截取
LENGTH(‘HelloWorld’) 10 字符串长度
INSTR(‘HelloWorld’, ‘W’) 6 查找一个子字符串，返回字符串第一次遇到的位置
LPAD(salary,10,’*’) *****24000 在左填充指定的字符串
RPAD(salary, 10, ‘*’) 24000***** 在右填充指定的字符串
REPLACE(‘JACK and JUE’,’J’,’BL’) BLACK and BLUE 字符串替换
TRIM(‘H’ FROM ‘HelloWorld’) elloWorld 去掉某字符
TRIM(‘ HelloWorld ‘) HelloWorld 去掉两边的空格
LTRIM(‘ HelloWorld’) HelloWorld 去掉左边的空格
RTRIM(‘HelloWorld ‘) HelloWorld 去掉右边的空格
ASCII(‘A’) 65 返回与指定字符对应的十进制数组
CHR(65) A 给出一个整数，并返回与之对应的字符 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
--CONCAT Examples
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP'; 
/**
EMPLOYEE_ID NAME   JOB_ID LENGTH(LAST_NAME) Contains 'a'?
----------- ------------- ---------- ----------------- ---------
202         PatFay MK_REP           3             2
**/
--TRIM TRIM enables you to trim leading or trailing characters (or both) from a character string. 
--Examples
SELECT employee_id,hire_date
      FROM employees
      WHERE department_id = 60
      ORDER BY employee_id;
/**
EMPLOYEE_ID HIRE_DATE
----------- ---------
        103 03-JAN-06
        104 21-MAY-07
        105 25-JUN-05
        106 05-FEB-06
        107 07-FEB-07
        301 03-FEB-99
        302 01-FEB-14
7 rows selected.
**/
SELECT employee_id,
      TO_CHAR(TRIM(LEADING 0 FROM hire_date))
      FROM employees
      WHERE department_id = 60
      ORDER BY employee_id; 
/**
EMPLOYEE_ID TO_CHAR(T
----------- ---------
        103 3-JAN-06
        104 21-MAY-07
        105 25-JUN-05
        106 5-FEB-06
        107 7-FEB-07
        301 3-FEB-99
        302 1-FEB-14
7 rows selected.
**/
/**
INSTR （string, substring, position, occurrence）
The following example searches the string CORPORATE FLOOR, beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:
**/
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
  "Instring" FROM DUAL;
 
 /**
  Instring
----------
        14
**/