CODEWITHSUNDEEP
sqloracle-databaseunicode
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How to remove double byte space from string in sql

I have a column in database table that holds Item Numbers. One of the item contains double byte space **400-000361（SZ17-20)**. When this item's details is exported from system, it removes this space in csv and shows Item Number like this **400-000361(SZ17-20)**

When I try to compare the exported data with the system's data using query it does not list this item because the number is now updated. Is there any way I can ignore that double space character to get the matched item using Regular Expression or some other way ? I tried following way but it does not work

SELECT REGEXP_REPLACE('400-000361（SZ17-20)', '\s{2,}', ' ') T FROM dual 
WHERE '400-000361(SZ17-20)' = TRIM(REGEXP_REPLACE('400-000361（SZ17-20)', '([[:space:]]{2,}|[[:cntrl:]])', ' '))

Upvotes: 0

Views: 650

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

You don't have a double space or a double-byte space, or in fact any space at all. You have a fullwidth left parenthesis, which is apparently being translated to a standard one in your export process.

You can see what you really have with dump():

SELECT dump('400-000361（SZ17-20)', 1016) FROM dual;

Typ=96 Len=21 CharacterSet=AL32UTF8: 34,30,30,2d,30,30,30,33,36,31,ef,bc,88,53,5a,31,37,2d,32,30,29
                                                                   ^^^^^^^^

and compare with the modified version:

SELECT dump('400-000361(SZ17-20)', 1016) FROM dual;

Typ=96 Len=19 CharacterSet=AL32UTF8: 34,30,30,2d,30,30,30,33,36,31,28,53,5a,31,37,2d,32,30,29
                                                                   ^^

You can convert your table data with replace() and the equivalent Unicode string:

SELECT REPLACE('400-000361（SZ17-20)', unistr('\ff08'), '(') T FROM dual 
WHERE '400-000361(SZ17-20)' = REPLACE('400-000361（SZ17-20)', unistr('\ff08'), '(')

400-000361(SZ17-20)

db<>fiddle

Upvotes: 1

Related Questions