工作中碰到一个字段(site)中包含了前置0(如,01,001,002等),在进行数据匹配查询时,这个前置时常会带来一些麻烦。如,
1. 在Excel中,如果双击这些前置0单元格,会发现Excel自动将其转换从“001”转换为“1”,即自动转换为数字格式(这是比较危险的事情,数据容易被污染)
2. 数据库不同表中,有的Site字段包含了前置0,有的又去掉了前置0,容易造成混乱
为了进行lookup操作,需要将这些前置0去掉,就需要使用LTRIM函数(相应的,有RTRIM函数)。
SELECT LTRIM('001', '0') As Site
FROM DUAL;
以下内容摘自《Oracle Database SQL Language Reference_11.2g》。
----------------------------------------------------------------------------------------------------------------
Purpose
LTRIM removes from the left end of char all of the characters contained in set. If you
do not specify set, then it defaults to a single blank. If char is a character literal, then
you must enclose it in single quotation marks. Oracle Database begins scanning char
from its first character and removes all characters that appear in set until reaching a
character not in set and then returns the result.
Both char and set can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2,
CLOB, or NCLOB. The string returned is of VARCHAR2 data type if char is a character data
type, NVARCHAR2 if char is a national character data type, and a LOB if char is a LOB
data type.
LTRIM removes from the left end of char all of the characters contained in set. If you
do not specify set, then it defaults to a single blank. If char is a character literal, then
you must enclose it in single quotation marks. Oracle Database begins scanning char
from its first character and removes all characters that appear in set until reaching a
character not in set and then returns the result.
Both char and set can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2,
CLOB, or NCLOB. The string returned is of VARCHAR2 data type if char is a character data
type, NVARCHAR2 if char is a national character data type, and a LOB if char is a LOB
data type.
Examples
The following example trims all the left-most occurrences of less than sign (<), greater
than sign (>) , and equal sign (=) from a string:
SELECT LTRIM('<=====>BROWNING<=====>', '<>=') "LTRIM Example"
FROM DUAL;
LTRIM Example
---------------
BROWNING<=====>