Non-breaking space
-
Lets see if i can explain this properly!
We have an xml file that is generating errors complaining that an invalid character has been found. Looking at the line of data it appears there are no invalid characters, however, opening the file in EditPlus shows that there is what i refer to as a non-breaking space in part of the data.
i.e in EditPlus a space is represented by a . so a sentence might be shown as:
the.quick.brown.fox
however, the rows that we're having problems with would be like:
the.quick brown.fox
the space not actually being represented by anything.
Unfortunately there are quite a few rows like this, is there any way that i can search for this 'non-breaking' space and replace it with a 'normal' space?
Thanks
Growing old is mandatory, growing up is optional -
Hopefully a bit closer to getting there!
By using this:
select ascii(substring(field4,19,1)), ascii(substring(field4,20,1)), ascii(substring(field4,21,1)),field4 from #tmp
i've discovered that the 20th character, the one thats causing the problem, has an ASCII code of 160
i've tried this:
update #tmp set field4 = replace(field4,ascii(160),ascii(60))
but nothing seems to happen.
any suggestions before i continue to pull my hair out?
Growing old is mandatory, growing up is optional -
I could swear I've seen an (almost) identical post to this one but can't seem to find it now...my search however threw up the following 2 links that may help...
btw...why're you replacing with ascii(60) & not ascii(32) ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for that, i'll see if any of the suggestions in those posts work.
I was using ascii(60) just as a test so it would stand out when i updated the row, its easier to see if the change happened rather than just seeing another space.
Cheers
Growing old is mandatory, growing up is optional -
I think that the fault is in REPLACE, I finally reverted to:
PRINT 'Fix leading CHAR(160)'
UPDATE MyTable
SET Phone = NULLIF(RTRIM(LTRIM(RIGHT(Phone,LEN(Phone)-1))),'')
WHERE ASCII(Phone) = 160
I never got REPLACE to work with CHAR(160), it works with others like CHAR(9), CHAR(13), or CHAR(10).
I was lucky that it was always a leading character.
Since you are working with a string, you have to change to:
REPLACE(field4,CHAR(160),CHAR(60))
ASCII returns the number, while CHAR returns the character value.
Andy
-
What a great start to the morning, thanks very much. I couldn't see the wood for the trees yesterday!
this is what i ended up with thanks to your post
update table
set field4 = replace(field4,char(160),char(32))
where patindex('%' + char(160) + '%',field4) > 0
Thanks again.
Growing old is mandatory, growing up is optional -
It should work just as fine with ascii 160 as any other.. I believe that it's a codepage problem rather than with replace itself.
ASCII 160 is in the high ASCII range, which means that the actual charachter depends on your codepage settings. For codepages 850 and 437 (US English) it's the letter á , but for Latin1 (ISO 8859-1) it's listed as an unprintable charachter (which looks like a space on paper at least
)/Kenneth
-
It appears that the codepage is 1252/Latin1 hence the unprintable character. Its only a problem when the web based version of the application trys to display it. The desktop version is fine.
Growing old is mandatory, growing up is optional -
Hi folks,
I have very similar problem. I need to remove all instances of high ascii (>128) but don't have regular place it appears. mainly as a result of users cut & pasting from strings from MS-Word with smart quotes ie quotes that curl left and right.. ascii 143 & 146 I think in most windows fonts.
Anyway I need to remove them anywhere they exist as they foul the xml parsing downstream.
Anyone help with a generic replace statement ?
or can I fix it simply if I change the collation on that table ? haven't mastered collation.. 🙁
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply