CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Dillon Tagle
Dillon Tagle

Reputation: 63

How to query for special characters

I have a large table filled with vendor information.

I need to split this list into two separate lists based on column VENDOR_NAME. One list where VENDOR_NAME is all regular characters and numbers, another list where VENDOR_NAME is special/foreign characters.

I am not sure what the SELECT statements would be to view this information off of the existing master table. Then I could just create two new tables.

  1. VENDOR_NAME only numbers and regular characters

  2. VENDOR_NAME only foreign characters

Example:

Regular: BLUE RIBBON TAG & LABEL CORP

Foreign: 俞章平

Regular: ULSTER-SOCIETY OF GASTROENTEROLOGY/1

Foreign: 马建忠

Upvotes: 0

Views: 1729

Answers (1)

Twinkles
Twinkles

Reputation: 1994

You could use the function ASCIISTR():

ASCIISTR takes as its argument a string, or an expression that resolves to a string, in any character set and returns an ASCII version of the string in the database character set. Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.

To get all strings without special characters:

SELECT * FROM table
WHERE INSTR(ASCIISTR(vendor_name),'\') = 0

You have to take care, of course, that strings with '\' would be filtered out by this as well, since the backslash is translated to '\005C' by ASCIISTR. Maybe like this:

WHERE INSTR(REPLACE(ASCIISTR(vendor_name),'\005C','_' ),'\') = 0

Upvotes: 2

Related Questions