- 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!
Reply to: Oracle double byte space...
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.
Logging in...
Previously on "Oracle double byte space..."
Collapse
-
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:
-
Originally posted by WTFH View PostI 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:
-
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:
-
Originally posted by WTFH View PostI 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.
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:
-
Originally posted by MrMarkyMark View PostA 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'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:
-
Originally posted by WTFH View PostThey 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 PostThe 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 PostI copied your string into SQL Developer and those aren't spaces
Leave a comment:
-
Ah, now that makes sense - Unicode character 3000 is an ideographic space. Tomorrow I will have a play in SQL.
Thanks!
Leave a comment:
-
Originally posted by WTFH View PostIt looks like this:
ド ||
Code:ド ||
(I've added a || at the end to make it easier to see)
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;
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
Code:RTRIM(REPLACE(txt,UNISTR('\3000'),' '))
Last edited by RonBW; 1 December 2016, 13:09.
Leave a comment:
-
Leave a comment:
-
Originally posted by WTFH View PostThey 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.
Leave a comment:
-
AS/400, blimey if it actually says that on the box it make a ZX81 look youthful.
Leave a comment:
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: