Post reply

High ASCII Characters in data

  • 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

     

  • June 7, 2007 at 5:35 pm

    #711556

    What do you mean "High ASCII Characters"?

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

    _____________
    Code for TallyGenerator

  • June 7, 2007 at 5:49 pm

    #711559

    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]

  • June 8, 2007 at 10:38 am

    #711754

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

    Thanks

  • June 8, 2007 at 11:00 am

    #711763

    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]

  • June 8, 2007 at 11:57 am

    #711771

    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.

  • June 8, 2007 at 12:34 pm

    #711781

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

    I'm deeply appreciate them.

    It works for me now.

    Minh Vu

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

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