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 06, 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: Yesterday @ 2:38 PM
Points: 958, Visits: 3,266
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 06, 2012 3:37 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:55 PM
Points: 145, Visits: 322
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 06, 2012 4:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:16 AM
Points: 88, Visits: 549
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 07, 2012 8:07 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:55 PM
Points: 145, Visits: 322
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 07, 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: Yesterday @ 2:38 PM
Points: 958, Visits: 3,266
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