SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


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

Home » SQL Server 7,2000 » T-SQL » Find and Replace Script Needed

Find and Replace Script Needed Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Brad
Posted Tuesday, October 14, 2008 7:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 18, 2009 9:56 PM
Points: 12, Visits: 40
Hi Guys,

I’d like the script to read a textfile on the same server [path is D:\Program Files\comshare\deciweb\BuildersW\Import_SAP_ACT\trial_balance.txt] and replace anything with the the values "B100", "B101", "B102", "B103", "B104", "B105" with "B98" and then all "B10" with "B98". Thereafter the script should replace anything with "S106", "S107" to "S90" and then all "S10" to "S90". The order is important in that the 100's must be done before the 10's and the quotes are not part of the string to be searched for, I just put them in to show what I'm after!

I would like to add this into a DTS package so I'm thinking I add some sort of script just after the package picks up the txtfile. What is best? SQL, ActiveX etc?

Please help. Thanks in advance for your help.

Regards,
Brad
Post #585466
Greg Snidow
Posted Wednesday, October 15, 2008 2:24 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: Monday, June 29, 2009 12:48 PM
Points: 823, Visits: 1,210
Can you import the file into a table, then update it there?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #586553
SJTerrill
Posted Wednesday, October 15, 2008 2:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 25, 2009 11:49 AM
Points: 313, Visits: 378
I'll agree with Greg. Get that data into a working table and update the values before moving on in your work flow. Or, use a stored procedure to BULK INSERT the data into a temp table and perform your updates against it...

--SJT--


Post #586567
steve block
Posted Thursday, October 16, 2008 8:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 02, 2009 12:32 PM
Points: 289, Visits: 769
If this were a one time event, I would use the search and replace capabilities of Query Analyzer but it sounds like you want to do this on a regular basis. Importing into a table and using the Replace function in an Update statement makes sense. Update it as many times as needed and in the order desired.

Steve


Post #586998
Brad
Posted Thursday, October 16, 2008 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 18, 2009 9:56 PM
Points: 12, Visits: 40
Thanks guys!

Will give it a shot.
Post #587345
Sergiy
Posted Thursday, October 16, 2008 9:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:14 PM
Points: 3,969, Visits: 5,334
Are you prepared to have all your "B100"to be replaced with "B980"?

Because you want to replace "B10" with "B98".
So, B100 as B10+0 will be replaced as B98+0 = B980.
Post #587404
Garadin
Posted Friday, October 17, 2008 6:39 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, June 11, 2009 11:23 AM
Points: 766, Visits: 1,909
Yes, but he also wants to replace B100 with B98, so as long as he does that replace first, he should be fine in that particular case. However, it is a very valid point for data being so similar, and if you have values like B106(which you don't list as wanting to replace), etc, you would need to take an additional step to ensure that those were not in your dataset. For example, instead of just using a replace across the whole table, specify something like the following:

UPDATE MyTable
SET Value = 'B98'
WHERE Value IN ('B101','B102','B103','B104','B105')

That way, you lower the risk of corrupting your data unintentionally.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots
Post #587580
« Prev Topic | Next Topic »


Permissions Expand / Collapse

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