Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Pipes at the End
Add to briefcase

Pipes at the End Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
AndrewSQLDBA
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: Wednesday, June 12, 2013 1:04 PM
Points: 807, Visits: 2,350
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
kylesway
Posted Tuesday, November 06, 2012 3:37 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 12:16 PM
Points: 95, Visits: 283
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
SQL_Kills
Posted Tuesday, November 06, 2012 4:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:07 PM
Points: 53, Visits: 405
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
kylesway
Posted Wednesday, November 07, 2012 8:07 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 12:16 PM
Points: 95, Visits: 283
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
AndrewSQLDBA
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: Wednesday, June 12, 2013 1:04 PM
Points: 807, Visits: 2,350
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.