Reply to: Oracle double byte space...

[NickFitz]

For future reference: Unicode spaces

[WTFH]

OK, so the RTRIM(REPLACE(txt,UNISTR('\3000'),' ')) solution didn't work where there were double-byte spaces between words.


I've managed to solve it in Excel by doing this:
=SUBSTITUTE(TRIM(SUBSTITUTE(D2,"　"," "))," ","　")


I've tried running similar in SQL:
Replace(TRIM(replace(txt,UNISTR('\3000'),' ')),' ',UNISTR('\3000')), but trim (or rtrim) doesn't seem to want to work on normal spaces when they appear after Unicode.
I've tried running the above split into 3 sections (replace all doubles with normal, then trim the end, then replace the singles with doubles), but to no avail.
For now, I'll just dump the table into Excel, clean it, then reload.


Thanks for all the help so far.

[MrMarkyMark]

I should add to the story... the ClientCo used to have a very good European IT department including 2 DBAs. In the past I would have fired the query over to them and let them solve it, they liked a challenge and it would have taken maybe half a day for them to play around and come up with a good solution.
ClientCo US decided to outsource IT functionality to one of the big Indian groups. Now, if I want to ask a question, I need to log a call, wait 2 days for a response, 5 days later they close the call because they don't know the answer, then a few days after that I contact them to find out that they have closed it but not told me (They user ServiceNow, which I don't have access to). Asking on here with a bunch of people who might know the answer, or have the intrigue to try to work it out, seemed much more sensible.

Nice to have others to share my exact pain, it makes it easier somehow .

The worst bit is they still do nothing even though I have told them exactly what they need to do technically, within a number of sub sytems, to give me exactly what I want and what the end consumer requires.

We are not launching rockets to the feckin' moon

[WTFH]

I should add to the story... the ClientCo used to have a very good European IT department including 2 DBAs. In the past I would have fired the query over to them and let them solve it, they liked a challenge and it would have taken maybe half a day for them to play around and come up with a good solution.
ClientCo US decided to outsource IT functionality to one of the big Indian groups. Now, if I want to ask a question, I need to log a call, wait 2 days for a response, 5 days later they close the call because they don't know the answer, then a few days after that I contact them to find out that they have closed it but not told me (They user ServiceNow, which I don't have access to). Asking on here with a bunch of people who might know the answer, or have the intrigue to try to work it out, seemed much more sensible.

[MrMarkyMark]

I know you're cheap!

I'll argue that they are spaces because they are generated by pressing the space bar on a Japanese keyboard. They aren't spaces in the European sense. I've discovered they are called ideographic spaces, because the space is the same size as an ideograph (that's a Chinese/Japanese character).

I'll try it out in a few mins when I get into the office.

Ah, the rod bent over with the strength of that bite, bloody good bait

I actually believe you will find out you are completely correct, just another reason to add a premium when you are working in Japan.

[WTFH]

A little bit of advice............in future do yourself a favour and pay for the true professionals to come in, I'm quite cheap, all told, really

I know you're cheap!

I'll argue that they are spaces because they are generated by pressing the space bar on a Japanese keyboard. They aren't spaces in the European sense. I've discovered they are called ideographic spaces, because the space is the same size as an ideograph (that's a Chinese/Japanese character).

I'll try it out in a few mins when I get into the office.

[MrMarkyMark]

They are definitely spaces, thanks for the help everyone, I've got the ClientCo to assign a permie in the US to the issue, meanwhile the IT manager out here is going to try to extract the data from their AS/400 without the spaces and send it to me again to upload.

The other possible thing is he could have unknown characters in there, also.
I had this once and only realised they were there after I had looked at the data in a hexadecimal editor.

I copied your string into SQL Developer and those aren't spaces

A little bit of advice............in future do yourself a favour and pay for the true professionals to come in, I'm quite cheap, all told, really

[WTFH]

Ah, now that makes sense - Unicode character 3000 is an ideographic space. Tomorrow I will have a play in SQL.

Thanks!

[RonBW]

It looks like this:


ド　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ||

ド　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　||

(I've added a || at the end to make it easier to see)

I copied your string into SQL Developer and those aren't spaces:

WITH xyz AS ( SELECT 'ド　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ' txt FROM DUAL
              UNION
              SELECT '1          ' from dual
              UNION
              SELECT '2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ' txt FROM DUAL
            )
SELECT txt
,      LENGTH(txt)
,      RTRIM(txt)
,      LENGTH(RTRIM(txt))
,      TRIM(txt)
,      LENGTH(TRIM(txt))
,      ASCIISTR(txt)
FROM   xyz;

Your string includes UNICODE characters:

\30C9\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000\3000

So the last character is a space but the rest of them aren't. You could include a REPLACE to remove those non-ASCII characters if that would help:

RTRIM(REPLACE(txt,UNISTR('\3000'),' '))

[eek]

Notepad ++ may tell a different story as eek has suggested

Offloading it to some poor sod not on cuk sounds like a far better plan and I heartedly approve of wtfh's approach

[mudskipper]

Dump might tell you what you're looking at.

https://www.techonthenet.com/oracle/functions/dump.php

[MrMarkyMark]

They are definitely spaces, thanks for the help everyone, I've got the ClientCo to assign a permie in the US to the issue, meanwhile the IT manager out here is going to try to extract the data from their AS/400 without the spaces and send it to me again to upload.

Notepad ++ may tell a different story as eek has suggested

[DimPrawn]

IBM i now....

It was renamed iSeries in 2000, keep up grandad.

Like calling your laptop now "an IBM PC compatible".

[stek]

IBM i now....

[DimPrawn]

AS/400, blimey if it actually says that on the box it make a ZX81 look youthful.