PL/SQL常用函数

一、常用字符函数

1、instr(string1, string2 [, start_position [,  nth_appearance]])

     解释：string1-源字符串，要在此字符串中查找；string2-要查找的字符串；start_position-代表从string1的那个位置开始查找，如果省略，默认为1。如果此参数为正，从左到右开始检索。如果此参数为负，从右到左检索。返回要查找的字符串在源字符创中的开始索引；nth_appearance代表要查找第几次出现的string2，如果省略，默认为1.如果为负数，系统会报错。如果string2在string1中没有找到，instr函数返回0.

     举例：select instr('abc', a) from dual;返回1

                  select instr('abc abc','a', 1, 2)  from dual。返回5

2、substr(char,start,length)

      解释：返回字符串表达式char中从第start开始的length个字符

      举例：substr('This is a test', 6, 2)     would return 'is'
                substr('This is a test', 6)         would return 'is a test'
                substr('TechOnTheNet', -3, 3)     would return 'Net'
                substr('abcde',-6) = null
                   substr('abcde',-5) = 'abcde'
                   substr('abcde',-2) = 'de'
                   substr('abcde',-1) = 'e'
                   substr('abcde',-0) = 'abcde'

 

3、trim()

     解释：删除字符串两边的空格

      举例：trim('   tech   ')  would return 'tech'；

 

4、trim([leading | trailing | both] trim_char from string)

     解释：从字符串String中删除指定的字符trim_char。leading:从字符串的头开始删除。 trailing:从字符串的尾部开始删除。borth:从字符串的两边删除。 tim()只能删除半角空格。 

     举例：trim(' '  from  '   tech   ')  would return 'tech'；

                  trim(leading '0' from '000123')      would return '123'；

                  trim(trailing '1' from 'Tech1') would return 'Tech'；

                  trim(both '1' from '123Tech111')    would return '23Tech'；

5、replace(char, search_string[, replacement_string])

      解释：每个search_string， 都被replacement_string代替。如果replacement_string为空,那么所有search_string都被移除。如果search_string为空，那么返回原来的char

      举例：select replace('abcd', 'cd', 'ef') from dual;    aefd

                 select replace('acdd','cd','')  from dual;        ad

                 select replace('acdd','','ef')  from dual;         acdd

                 select replace('acdd', '', '')  from dual;          acdd

 6、round( number, decimal_places )

      解释：用来对数据进行四舍五入,number --需四舍五入处理的数值,decimal_places --四舍五入 , 小数取几位 ( 预设为 0 )返回值类型数字例子

      举例：select round(123.456, 0) from dual; 回传 123 
                 select round(123.456, 1) from dual; 回传 123.5 
                 select round(123.456, 2) from dual; 回传 123.46 

 

7、 round( date, [ format ] )

      解释：用来对日期进行四舍五入，the round function returns a date rounded to a specific unit of measure；date is the date to round；format is the unit of measure to apply for rounding. If the format parameter is omitted, the round function will round to the nearest day.

      举例：  

round(to_date ('22-AUG-03'),'YEAR')	would return '01-JAN-04'
round(to_date ('22-AUG-03'),'Q')	would return '01-OCT-03'
round(to_date ('22-AUG-03'),'MONTH')	would return '01-SEP-03'
round(to_date ('22-AUG-03'),'DDD')	would return '22-AUG-03'
round(to_date ('22-AUG-03'),'DAY')	would return '24-AUG-03'

 

Below are the valid format parameters:

Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	Rounds up on July 1st
ISO Year	IYYY, IY, I
Quarter	Q	Rounds up on the 16th day of the second month of the quarter
Month	MONTH, MON, MM, RM	Rounds up on the 16th day of the month
Week	WW	Same day of the week as the first day of the year
IW	IW	Same day of the week as the first day of the ISO year
W	W	Same day of the week as the first day of the month
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

 

 

二、常用日期函数

1、trunc（day, [fmt]）

      解释：按照fmt指定格式对日期数据day做舍入处理，默认截断到日