Pipes at the End

Question

Pipes at the End

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
November 6, 2012 at 3:29 pm

#393549

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

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

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

KylesWay Old Hand Points: 369 More actions · Answer 1

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?

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

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))

KylesWay Old Hand Points: 369 More actions · Answer 3

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.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Yes, thank you

That worked perfectly

Thanks again for your assistance

Andrew SQLDBA