ASCII Text Dump

  • saga

    SSC Veteran

    Points: 242

    Comments posted to this topic are about the item ASCII Text Dump

  • steve.casey

    Old Hand

    Points: 310

    Suppose you have column ID in table T. Try the following:

    Select ID, CAST(ID as Varbinary(255)) as Hex From T.

    I find this to be sufficient in almost every case (and I strongly prefer to see the values in hex!).

  • Jeff Moden

    SSC Guru

    Points: 995116

    steve.casey wrote:

    Suppose you have column ID in table T. Try the following:

    Select ID, CAST(ID as Varbinary(255)) as Hex From T.

    I find this to be sufficient in almost every case (and I strongly prefer to see the values in hex!).

    Just my 2 cents.  Normally, I'm looking for characters out of the ordinary.  I also normally have a whole lot of data in files that I want to import or already have imported to check.  I don't want to be eyeballing literally a billion or more hexadecimal bytes of information.  The method in the article and the cast, in the absence of everything else, would require such eyeballing.

    That being said, why not just bring a Tally function into play an let the computer quickly isolate that which you've been eyeballing?  Here's a very simple example for just one string.

    DECLARE @SomeString VARCHAR(MAX) = 'The quick brown fox jumpedá over the lazy dogs.
    You should have seen what the cats did.'
    ;
    SELECT StringPosition = t.N
    ,Character = SUBSTRING(@SomeString,t.N,1)
    ,ASCII_Dec = RIGHT(1000+ASCII(SUBSTRING(@SomeString,t.N,1)),3)
    ,ASCII_Hex = CONVERT(CHAR(2),CONVERT(BINARY(1),(SUBSTRING(@SomeString,t.N,1))),2)
    FROM dbo.fnTally(1,DATALENGTH(@SomeString)) t
    WHERE ASCII(SUBSTRING(@SomeString,t.N,1)) NOT BETWEEN 32 AND 126
    ;

    Here are the results from that code.

    If you don't already have a "Tally" function, the one I use for production code is at the link in my signature line below.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

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