Oracle double byte space...

[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]

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]

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 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]

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.

[NickFitz]

For future reference: Unicode spaces