(ORA-00023) - Tách rời các giá trị Numeric/Non-Numeric/Date trong cột

Tách rời các giá trị Numeric/Non-Numeric/Date
Oracle SQL - Làm cách nào để tìm các giá trị là Numeric/Non-Numeric/Date trong cột?

Có nhiều cách để tách giá trị số và ký tự từ một cột. Hãy cùng tìm hiểu một vài cách dưới đây:
- Sử dụng hàm TRANSLATE
- Sử dụng biểu thức toán học
- Sử dụng hàm DATE
- Sử dụng Regular Expressions (biểu thức chính quy)

Hãy lấy một ví dụ trong bảng dưới đây.

SQL> SELECT NAME FROM DUP_TEST;

NAME
----------
10001

67671
23423
ASDAS
HELLO

OK

123

10 rows selected.

SQL> SELECT NVL(NAME,'NA') FROM DUP_TEST;

NVL(NAME,'NA')
--------------
10001
NA
67671
23423
ASDAS
HELLO
NA
OK
NA
123

10 rows selected.

1. Sử dụng hàm TRANSLATE: thay thể một chuỗi các ký tự bằng một chuỗi các ký tự thay thế.
Lấy danh sách các giá trị là ký tự chữ cái:

SQL> SELECT NAME FROM DUP_TEST
WHERE LENGTH(TRANSLATE(NAME,'x0123456789','x')) IS NOT NULL;

NAME
----------
ASDAS
HELLO
OK

3 rows selected.

Lấy danh sách các giá trị là ký tự số:

SQL> SELECT NAME FROM DUP_TEST
WHERE LENGTH(TRANSLATE(NAME,'x0123456789','x')) IS NULL
AND NAME IS NOT NULL;

NAME
----------
10001
67671
23423
123

4 rows selected.

Biểu thức "LENGTH(TRANSLATE(NAME,'x0123456789','x'))" sẽ trả về NULL nếu cột NAME chứa các giá trị là ký tự số.

SQL> SELECT NVL2(LENGTH(TRANSLATE('123','x123','x')),'NOT NULL','NULL') COL FROM DUAL;

COL
----
NULL
1 row selected.

SQL> SELECT NVL2(LENGTH(TRANSLATE('abc','x123','x')),'NOT NULL','NULL') COL FROM DUAL;

COL
--------
NOT NULL
1 row selected.

2. Sử dụng MATH - biểu thức toán học:

SQL> SELECT '12345'/1 COL FROM DUAL;

COL
----------
12345
1 row selected.

SQL> SELECT '0012345'/1 COL FROM DUAL;

COL
----------
12345
1 row selected.

SQL> SELECT 'hello'/1 COL FROM DUAL;

SELECT 'hello'/1 COL FROM DUAL
*
Error at line 1
ORA-01722: invalid number

Dựa trên các ví dụ cơ bản ở trên, ta có thể viết một PL/SQL block/Procedure/Function để tách các ký tự chữ cái và số.

DECLARE
v_RESULT NUMBER;
CURSOR c_CC IS SELECT NAME FROM DUP_TEST WHERE NAME IS NOT NULL;
BEGIN
FOR i IN c_CC
LOOP
BEGIN
SELECT i.NAME/1 INTO v_RESULT FROM DUAL;
DBMS_OUTPUT.PUT_LINE(i.NAME || ' is a Number');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(i.NAME || ' is NOT a Number');
END;
END LOOP;
END;
/
10001 is a Number
67671 is a Number
23423 is a Number
ASDAS is NOT a Number
HELLO is NOT a Number
OK is NOT a Number
123 is a Number

PL/SQL procedure successfully completed.

3. Sử dụng hàm DATE:

SQL> SELECT TO_CHAR(TO_DATE(001993,'J'),'JSP') "WORD" FROM DUAL;

WORD
--------------------------------------
ONE THOUSAND NINE HUNDRED NINETY-THREE

1 row selected.

Dựa trên ví dụ ở trên, ta có thể viết một PL/SQL block/Procedure/Function để tách các ký tự chữ cái và số.

DECLARE
v_RESULT VARCHAR2(300 CHAR);
CURSOR c_CC
IS SELECT NAME FROM DUP_TEST WHERE NAME IS NOT NULL;
BEGIN
FOR i IN c_CC
LOOP
BEGIN
SELECT TO_CHAR(TO_DATE(i.NAME,'J'),'JSP') INTO v_RESULT FROM DUAL;
DBMS_OUTPUT.PUT_LINE(i.NAME || ' is a Number');
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(i.NAME || ' is NOT a Number');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
END;
/
10001 is a Number
67671 is a Number
23423 is a Number
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01858: a non-numeric character was found where a numeric was expected

123 is a Number

PL/SQL procedure successfully completed.

Exception VALUE_ERROR sẽ không bắt lỗi ORA-01858 và đó là lý do tại sao trả ra dòng lỗi SQL. Ngoài ra bạn có thể viết User Define Exception để hiển thị theo ý mình.

4. Sử dụng Biểu thức Chính quy:
Sử dụng hàm REGEXP_LIKE sẽ giống như dùng hàm TRANSLATE ở mục 1.

SQL> SELECT NAME FROM DUP_TEST
WHERE REGEXP_LIKE(NAME,'^[[:digit:]]+$');

NAME
----------
10001
67671
23423
123

4 rows selected.

Hãy tham gia và để lại nhận xét của bạn vào bên dưới. Càng nhiều người tham gia, chúng ta càng được hưởng lợi nhiều =)). Vì vậy, để lại những suy nghĩ của bạn trước khi rời khỏi trang nhé.