Post reply

Identifying hidden characters (pipes)

  • April 21, 2011 at 3:34 am

    #389344

    Hi there

    At my firm we are currently having issues where users are copying and pasting information in to our systems.

    most of the time this inputs in to our tables fine but on occasions it causes a problem where it puts characters at the end which aren't normal spaces but what seems to be pipes.

    this then causes problems where some queries are run and these pipes push information down on the results output.

    has anybody else had this problem and got a solution where i can first identify them and also remove.

    thanks

    James

  • April 22, 2011 at 2:17 am

    #1314803

    A pipe within a column would give simply a column with a pipe in it 🙂

    It probably wont be what you want but the db can handle it.

    So assuming thats not your problem are you getting this issue whrn importing/exporting delimited data?

    More info required to be able to help.

    ---------------------------------------------
    If data can screw you, it will; never assume the data are correct.

  • April 26, 2011 at 2:43 am

    #1315715

    Thanks for your reply

    we don't believe it is adding an extra column but a character return (adds a row) due to this "pipe".

    if we import data we normally use the applications importing tools so that will tell us it is failing.

    the problem is occurring from copying and pasting from other applications/documents.

    we just need a way of differentiating between just a blank spaces and a pipe.

    hope that explains it slightly better.

  • April 26, 2011 at 3:44 am

    #1315733

    -- What is the odd character and how many rows are affected?

    SELECT ASCII(LEFT(REVERSE(MyColumn),1)) FROM MyTable WHERE ASCII(LEFT(REVERSE(MyColumn),1)) < 48

    -- How does this work?

    DECLARE @MyString VARCHAR(15)

    SET @MyString = 'Something' + CHAR(13)

    SELECT @MyString

    SELECT REVERSE(@MyString)

    SELECT LEFT(REVERSE(@MyString),1)

    SELECT ASCII(LEFT(REVERSE(@MyString),1))

    SELECT ASCII(0), ASCII(9), ASCII('A'), ASCII('Z'), ASCII('a'), ASCII('z')

    There is a chance that the odd character won't get picked up:

    select ASCII('|')

    You could use wildcards with the LIKE operator or simply change the filter in the query above to cover all numbers 0-9 and all characters.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • April 26, 2011 at 5:01 am

    #1315761

    This looks like it is going to help with our identifcation of characters we don't want in our systems. Mainly ASCII code 10 which is LF (line feed).

    Thanks guys for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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