Oracle：内置函数--字符函数

  1、ASCII()和CHR()

sql 代码

DECLARE
    V_VARCH VARCHAR2(5) :='X';
BEGIN
    --返回'X'的ASCII码,输出88
    DBMS_OUTPUT.put_line(ASCII(V_VARCH));
    --返回ASCII码为88的字符，输出‘X’
    DBMS_OUTPUT.put_line(CHR(88));
END;

   
    2、CONCAT()拼接字符

sql 代码

DECLARE
    V_VARCH_FULLNAME VARCHAR2(50);
BEGIN
    SELECT CONCAT(C.first_name,C.last_name)
    INTO V_VARCH_FULLNAME
    FROM CUSTOMERS C
    WHERE C.customer_id = 1;
    --返回customer_id=1的消费者的全名
    DBMS_OUTPUT.put_line(V_VARCH_FULLNAME);
    --单独字符串操作
    V_VARCH_FULLNAME :=CONCAT('X','Y');
    DBMS_OUTPUT.put_line(V_VARCH_FULLNAME);
END;

    CONCAT(X,Y)函数的作用是将Y列的值拼接在X列值的后面。类似“||”的功能

sql 代码

SELECT C.first_name ||' ' || C.last_name AS FULLNAME
FROM CUSTOMERS C
WHERE C.customer_id=1;

   
    3、INITCAP()   将每个单词的首字母转换为大写。

sql 代码

DECLARE
    V_VARCH_RETURN VARCHAR2(50);
BEGIN
    SELECT INITCAP(P.description)
    INTO V_VARCH_RETURN
    FROM PRODUCTS P
    WHERE P.product_id = 1;
    --返回product_id=1的产品信息。
    --输出：A Description Of Modern Science
    --原数据为：A description of modern science
    DBMS_OUTPUT.put_line(V_VARCH_RETURN);
    --对字符串进行操作
    V_VARCH_RETURN := INITCAP('A description of modern science');
    --输出结果一致
    DBMS_OUTPUT.put_line(V_VARCH_RETURN);
END;

    4、INSTR(x,find_string[,start][,occurrence])函数用于在x中查找find_string，返回find_string的所在位置。

sql 代码

DECLARE
    V_INT_INDEX NUMBER;
BEGIN
    --在AAA中查找‘A’，从第1个字符开始找，返回第2次出现的位置
    V_INT_INDEX := INSTR('AAA','A',1,2);
    DBMS_OUTPUT.put_line(V_INT_INDEX);
    --在NAME中查找‘Science’，缺省从第一位开始找，返回第一次出现的位置。
    SELECT INSTR(P.name,'Science')
    INTO V_INT_INDEX
    FROM PRODUCTS P
    WHERE P.product_id = 1;
    DBMS_OUTPUT.put_line(V_INT_INDEX);
END;

    5、LENGTH(x)返回字符串x的长度

sql 代码

DECLARE
    V_VARCH_RETURN NUMBER;
BEGIN
    V_VARCH_RETURN :=LENGTH('KOOK');
    --输出4
    DBMS_OUTPUT.put_line(V_VARCH_RETURN);
END;

    6、LOWER()和UPPER()完成大小写转换

sql 代码

DECLARE
    V_VARCH_RETURN VARCHAR2(10) :='KOOK';
BEGIN
    --大写转小写
    DBMS_OUTPUT.put_line(LOWER(V_VARCH_RETURN));
    --小写转大写
    DBMS_OUTPUT.put_line(UPPER(V_VARCH_RETURN));
END;

    7、LPAD()和RPAD()

sql 代码

DECLARE
    V_VARCH_RETURN VARCHAR2(10) :='KOOK';
BEGIN
    --用左边填补空格，凑足8个字符
    DBMS_OUTPUT.put_line(LPAD(V_VARCH_RETURN,8));
    --用左边填补‘X’，凑足8个字符
    DBMS_OUTPUT.put_line(LPAD(V_VARCH_RETURN,8,'X'));
    --用右边填补空格，凑足8个字符
    DBMS_OUTPUT.put_line(RPAD(V_VARCH_RETURN,8));
    --用右边填补‘X’，凑足8个字符
    DBMS_OUTPUT.put_line(RPAD(V_VARCH_RETURN,8,'X'));

END;

    还可以对数字运用该函数

sql 代码

SELECT RPAD(P.name,30,'.'),LPAD(P.price,8,'*+')
FROM PRODUCTS P
WHERE P.product_id < 4;

    PRICE列为NUMBER类型。

    8、LTRIM()、RTRIM()和TRIM()
    LTRIM(x[,trim_string])函数如果省略trim_string参数表示从x的左边截去空格，否则从x的左边截去 trim_string部分。RTRIM表示从右边做相同的动作，TRIM表示从两边做相同的动作，但是使用上和另外两个有点不同，具体参考下面的例子：

sql 代码

DECLARE
    V_VARCH_RETURN VARCHAR2(50):='KOOK';
BEGIN
    --从左边截去‘K’
    DBMS_OUTPUT.put_line(LTRIM(V_VARCH_RETURN,'K'));
    --从右边截去‘K’
    DBMS_OUTPUT.put_line(RTRIM(V_VARCH_RETURN,'K'));
    --从两边截去‘K’
    DBMS_OUTPUT.put_line(TRIM('K' FROM 'KOOK'));
    --从两边截去空格
    DBMS_OUTPUT.put_line(TRIM(' ' FROM ' KOOK '));
    DBMS_OUTPUT.put_line(TRIM(' KOOK '));
END;

    9、NVL()和NVL2()
       NVL(x,value)如果x为NULL，就返回value，否则返回x。
       NVL2(x,value1,value2)如果x不为NULL，返回value2,否则返回value2。

sql 代码

DECLARE
    V_VARCH_RETURN VARCHAR2(50):='KOOK';
BEGIN
    V_VARCH_RETURN := NVL(NULL,'HAVE NO VALUE');
    DBMS_OUTPUT.put_line(V_VARCH_RETURN);

    SELECT NVL2(C.phone,C.phone,'UNKNOW')
    INTO V_VARCH_RETURN
    FROM CUSTOMERS C
    WHERE C.customer_id = 5;
    DBMS_OUTPUT.put_line(V_VARCH_RETURN);

    --无法使用 NVL2直接操作，下面语句报错！
    --V_VARCH_RETURN := NVL2('XXX','HAVE VALUE','HAVE NO VALUE');
    --V_VARCH_RETURN := NVL2(NULL,'HAVE VALUE','HAVE NO VALUE');
    --DBMS_OUTPUT.put_line(V_VARCH_RETURN);

END;

    10、REPLACE()函数
       REPLACE(X,STR1,STR2)在X中找到STR1,并将其换成STR2。

sql 代码

DECLARE
    V_VARCH_RETURN VARCHAR2(50):='KOOK';
BEGIN
    --输出‘KEEK’
    DBMS_OUTPUT.put_line(REPLACE(V_VARCH_RETURN,'OO','EE'));
END;

    11、SUBSTR()函数
       SUNSTR(X,START[,LENGTH])用于从X中取得从START位置开始的一个子字符串，还可以使用可选参数LENGTH指定子字符串的长度。省略LENGTH表示从START开始一直到最后一个字符。

sql 代码

DECLARE
    V_VARCH_RETURN VARCHAR2(50):='KOOK';
BEGIN
    --从第二个字符开始，一直取到末尾，输出 ‘OOK’
    DBMS_OUTPUT.put_line(SUBSTR(V_VARCH_RETURN,2));
    --从第二个字符开始，取两个字符长度，输出‘OO’
    DBMS_OUTPUT.put_line(SUBSTR(V_VARCH_RETURN,2,2));
END;

    12、SOUNDEX()函数
       SOUNDEX(X)函数用于获得包含X发音的一个字符串，该函数用于对于英文拼写不同但发音相似的单词间的比较。

sql 代码

DECLARE
    V_VARCH_A VARCHAR2(50):='WHITE';
    V_VARCH_B VARCHAR2(50):='WHYTE';
    V_VARCH_C VARCHAR2(50):='KOOK';
BEGIN
    IF SOUNDEX(V_VARCH_A)=SOUNDEX(V_VARCH_C) THEN
        DBMS_OUTPUT.put_line('A SOUND LIKE C');
    ELSIF SOUNDEX(V_VARCH_A)=SOUNDEX(V_VARCH_B) THEN
        --正确输出结果
        DBMS_OUTPUT.put_line('A SOUND LIKE B');
    ELSIF SOUNDEX(V_VARCH_C)=SOUNDEX(V_VARCH_B) THEN
        DBMS_OUTPUT.put_line('C SOUND LIKE B');
    ELSE
        DBMS_OUTPUT.put_line('NO RESULT');
    END IF;
END;