Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pipes at the End Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 3:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #1381732
Posted Tuesday, November 6, 2012 3:37 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:06 AM
Points: 163, Visits: 367
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?
Post #1381735
Posted Tuesday, November 6, 2012 4:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 2:12 PM
Points: 118, Visits: 623
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))

Post #1381742
Posted Wednesday, November 7, 2012 8:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:06 AM
Points: 163, Visits: 367
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.
Post #1382005
Posted Wednesday, November 7, 2012 8:12 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
Yes, thank you
That worked perfectly

Thanks again for your assistance

Andrew SQLDBA
Post #1382008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse