Oracle SQL removes Chinese characters and retains numbers or letters

Oracle SQL removes Chinese characters and retains numbers or letters. First, let's talk about oracle translate usage: 1. Syntax: TRANSLATE (string, from_str, to_str) 2. Purpose: Return (all appear) replace each character in from_str with the string after the corresponding character in to_str. TRANSLATE Is a superset of the functions provided by REPLACE. If from_str is longer than to_str, extra characters in from_str instead of to_str will be deleted from the string because they do not have replacement characters. To_str cannot be blank. Oracle interprets the NULL String as NULL, and if any parameter in the TRANSLATE Is NULL, the result is also NULL. 3. transate example dw @ dw> column col1 format a10dw @ dw> column col2 format a10dw @ dw> select translate ('helloword', 2 'abcdefghijklmnopqrstuvwxy', 3 'hangzhou ') col1, 4 translate ('hbcadhlk', 5' abcdefghijklmnopqrstuvwxyz ', 6 '000000') col2 7 from dual; COL1 COL2 ---------- svooldliw 123456 has selected 1 line. Time in use: 00: 00: 00.01 in actual work, we often use to remove Chinese characters and retain numbers or letters. It may also be to remove numbers or letters and retain Chinese characters. This article uses the oracle SQL tranlsate function to remove Chinese characters and retain numbers; remove numbers and mothers and retain Chinese characters. The SQL statements used in this article are as follows: create table oracle_extract_number (id number, name varchar2 (50); insert into oracle_extract_number (id, name) values (1, '32 extract 11222 of 333 Chinese characters, 44411 words, 11 '); insert into oracle_extract_number (id, name) values (1, 'ad extracts the 3ddd33 number 444dd11 word 11dddfw222 from Chinese characters '); insert into oracle_extract_number (id, name) values (1, 'abd2 extracts 11222 of 333 Chinese characters 44ddd411 '); insert into ora Cle_extract_number (id, name) values (1, '45 extract 233 of 1ddd1222ddd's 333 Chinese characters, 44411 words 11'); insert into oracle_extract_number (id, name) values (1, '78 extract the 33ddad3 number 44ddfdf411 word 11' of the sequence sdd22 from Chinese characters); commit; 1. remove Chinese characters and letters with oracle translate and extract numbers dw @ dw> select id, name, 2 translate (name, '#' | 3 translate (name, '123 ', '#'), '/') "oracle extract number" 4 from oracle_extract_number; id name oracle extract number ------------------ -------------------------------- 1 32 extract 11222 of Chinese characters 333 of 44411 words 11 32112223334441111 2 ad extract 3ddd33 of 11dddfw222 from Chinese characters 444dd11 11 112223334441111 3 abd2 extract 11222 of 333 of Chinese characters 44ddd411 2112223334441111 4 45 extract 233 numbers of 1 ddd1222ddd from 333 Chinese characters 44411 words 11 45233112223334441111 5 78 extract 33ddad3 number 44ddfdf411 word 11 78112223334441111 in the Chinese characters have selected 5 rows. Used time: 00: 00: 00. 032. remove Chinese characters with oracle translate and extract numbers and letters dw @ dw> select id, name, 2 translate (name, '#' | 3 translate (name, 'alipay ', '#') 4, '/') "oracle extract number" 5 from oracle_extract_number; id name oracle extract number ---------- extract ---------------------------- 1 32 extract 11222 of Chinese characters, 333 words, 44411 words, 11 32112 words 223334441111 2 ad extract Chinese characters 11dddfw222 3ddd33 number 444dd11 word 11 comment 3 abd2 extract 11222 number 44ddd411 word 11 comment 4 45 extract Chinese characters 1 ddd1222ddd 333 number 233 words 11 452331ddd1222ddd3334441111 5 78 extract the 33ddad3 number 44ddfdf411 11 781_sdd2233ddad344ddfdf41111 in the Chinese characters. You have selected 5 rows. Used time: 00: 00: 00. 003. remove numbers and letters with oracle translate, extract Chinese characters dw @ dw> select id, 2 name, 3 translate (name, 4 '0123456789abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz ', 5 '/') "oracle extract number" 6 from oracle_extract_number; id name oracle extract number ---------- extract --------------------------- 1 32 extract 11222 of Chinese characters, 333 of Chinese characters, 44411 of Chinese characters, 11 extract numbers from Chinese characters, 2 ad extract 3 of 11dddfw222 from Chinese Characters Ddd33 number 444dd11 word 11 extract numbers from Chinese Characters 3 abd2 extract 11222 of 333 in Chinese characters 44ddd411 word 11 extract numbers 4 45 extract numbers from Chinese characters 1 ddd1222ddd number 233 words 11 Extract the number 5 78 in the Chinese character is extracted from the 33ddad3 number 44ddfdf411 in the Chinese character of the sdd22. Five rows have been selected for the number extracted from the Chinese character. Used time: 00: 00: 00.01