High ASCII Characters in data

Question

Post reply

High ASCII Characters in data

joemai

Hall of Fame

Points: 3496
More actions
June 7, 2007 at 5:14 pm

#117513

Is there away to identify the High ASCII Characters in data of a table. Is there away to remove them. I really need help from the Experts.
Thanks
Minh Vu

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

What do you mean "High ASCII Characters"?

Is it characters having ASCII(@Char) >= 128?

_____________
Code for TallyGenerator

JacekO SSCertifiable Points: 6610 More actions · Answer 2

If I understand your request correctly then the code below might work or at least point you in the right direction

UPDATE yourtable

SET

yourfield = LEFT(yourfield, PATINDEX('%[^''a-Z .,-]%', yourfield) - 1) + SUBSTRING(yourfield, PATINDEX('%[^''a-Z .,-]%', yourfield) + 1, LEN(yourfield))

WHERE PATINDEX('%[^''a-Z .,-]%', yourfield) > 0

It removes only one character at a time so you may have to run it few times or convert this to a recursive function. It will remove any character that is not a letter or comma, dot, single quote, space and dash.

I just tested it on the last name field and the characters listed above were valid in the last name field

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

joemai Hall of Fame Points: 3496 More actions · Answer 3

yes, that is what i'm looking for. I'd like to know how to find them.

Thanks

JacekO SSCertifiable Points: 6610 More actions · Answer 4

In this case just replace the

'%[^''a-Z .,-]%'

with this

'%[^' + CHAR(128) + '-' + CHAR(255) + ']%'

and see if it works for you

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 5

Try this:

-------------------------------------------------------------------

Declare @i Int,@Str VarChar(8000)

-- Setup test string with weird stuff in it.

Set @STR=Char(7)+'Line1'+Char(9)+'Line2'+Char(255)

Print @STR

Set @i=PatIndex('%[^'+Char(32)+'-'+char(126)+']%' collate Latin1_General_BIN,@Str)

While @i<>0 Select @STR=Replace(@Str,SubString(@Str,@i,1),' '),

@i=PatIndex('%[^'+Char(32)+'-'+char(126)+']%' collate Latin1_General_BIN,@Str)

Print @STR

-------------------------------------------------------------------

Create a UDF with something like the above. You may need to alter the Collate statement for your environment.

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

joemai Hall of Fame Points: 3496 More actions · Answer 6

Thank you so much for all of ex...ex...excellent help.

I'm deeply appreciate them.

It works for me now.

Minh Vu