Pipes at the End

  • Hello Everyone

    I am loading some data, not clean and not from another SQL source. So there are all kinds of things mixed in the data.

    At the very end of each row in the table, in the very last column, I have a blank space followed by two pipes ( ||). I need to delete the blank space and the two pipe, but none of the data in front of that in the same column. Each row is different for that particular column. But this odd charachter mix is always at the very end.

    How can I most efficiently delete the blank space and the two pipes

    Thank you in advance for you help

    Andrew SQLDBA

  • You could try altering the 'raw' data. If this is in txt form (or is you can get it there), you could use a Ctrl + H (Find and Replace). Then try your import. That may work. Do you have that option?

  • Hi,

    Can you not add a update statement at the end of the package, which update all that column and removes any pipeline characters?

    update yourtablename

    set yourcolumnname = REPLACE(<columnname>,char(124),char(32))

  • I went back and revisited this. The code below should work for you. I created a Table with a Column in it. Added text that ended with ' ||' and then ran this against it.

    UPDATE [dbo].[Table1]

    SET [Col3] = (SELECT SUBSTRING(Col3, 1, + CHARINDEX(' ||',Col3, -1)))

    GO

    Good luck.

  • Yes, thank you

    That worked perfectly

    Thanks again for your assistance

    Andrew SQLDBA

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

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