• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Collapse

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

  • You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
  • You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
  • If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Previously on "Oracle double byte space..."

Collapse

  • NickFitz
    replied
    For future reference: Unicode spaces

    Leave a comment:


  • WTFH
    replied
    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.

    Leave a comment:


  • MrMarkyMark
    replied
    Originally posted by WTFH View Post
    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

    Leave a comment:


  • WTFH
    replied
    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.

    Leave a comment:


  • MrMarkyMark
    replied
    Originally posted by WTFH View Post
    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.

    Leave a comment:


  • WTFH
    replied
    Originally posted by MrMarkyMark View Post
    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.

    Leave a comment:


  • MrMarkyMark
    replied
    Originally posted by WTFH View Post
    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.
    Originally posted by MrMarkyMark View Post
    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.

    Originally posted by RonBW View Post
    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

    Leave a comment:


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

    Thanks!

    Leave a comment:


  • RonBW
    replied
    Originally posted by WTFH View Post
    It looks like this:


    ド                                                  ||


    Code:
    ド                                                 ||

    (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:

    Code:
    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:

    Code:
    \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:

    Code:
    RTRIM(REPLACE(txt,UNISTR('\3000'),' '))
    Last edited by RonBW; 1 December 2016, 13:09.

    Leave a comment:


  • eek
    replied
    Originally posted by MrMarkyMark View Post
    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

    Leave a comment:


  • mudskipper
    replied
    Dump might tell you what you're looking at.

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

    Leave a comment:


  • MrMarkyMark
    replied
    Originally posted by WTFH View Post
    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

    Leave a comment:


  • DimPrawn
    replied
    Originally posted by stek View Post
    IBM i now....
    It was renamed iSeries in 2000, keep up grandad.

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

    Leave a comment:


  • stek
    replied
    IBM i now....

    Leave a comment:


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

    Leave a comment:

Working...
X