「Oracle」REPLACE関数で文字列を置換する方法

2021年8月3日

1.SELECT文
テーブルUSER_TBLのTARGET列の値が0の場合、NULLに置換
使用例

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT
REPLACE (TARGET, 0, NULL)
FROM
USER_TBL;
SELECT REPLACE (TARGET, 0, NULL) FROM USER_TBL;
SELECT
  REPLACE (TARGET, 0, NULL) 
FROM
  USER_TBL;

2.UPDATE文
テーブルUSER_TBLのTARGET列の値が0の場合、NULLに置換
使用例

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
UPDATE USER_TBL
SET
TARGET = REPLACE (TARGET, 0, NULL);
UPDATE USER_TBL SET TARGET = REPLACE (TARGET, 0, NULL);
UPDATE USER_TBL 
SET
  TARGET = REPLACE (TARGET, 0, NULL);

3.INSERT-SELECT文
テーブルINFO_TBLのTARGET列の値が0の場合、NULLに置換
使用例

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
INSERT
INTO USER_TBL
SELECT
REPLACE (TARGET, 0, NULL)
FROM
INFO_TBL;
INSERT INTO USER_TBL SELECT REPLACE (TARGET, 0, NULL) FROM INFO_TBL;
INSERT 
INTO USER_TBL 
SELECT
  REPLACE (TARGET, 0, NULL) 
FROM
  INFO_TBL; 

 

Oracle

Posted by arkgame