REPLACE function help!

Question

REPLACE function help!

cmartinz

SSC Eights!

Points: 941
More actions
January 27, 2004 at 3:48 pm

#107151

Hello to all. I have to create some text files to be sent on daily basis. This was done without any problem. The problem exists, whe the other end tries to load the text file.
They requested to use double pipe as text delimiter, done. There are some records in which the end user typed a single pipe (456123|JOSHUA's TEAM|)
When the receiver tries to load this record, the way they set the system rolls back and stop loading the file.
I thought that maybe a REPLACE could help to make the change to that single pipe to something else, let's say ~
Just to give you and idea of the situation here is a sample data for some of those instances. Using a simple select:
SELECT     ACCT_NO
FROM         CLAIM_TAB
WHERE     (ACCT_NO LIKE '%|')
I got:
Account Number
001-4017048|
03028005133321|
0501347|CL2947.65|
1230117148|TOPAZ|
1343042~/SC|1355947|
1520170380|
Then I use query analyzer to replace the single pipe
SELECT REPLACE([ACCT_NO],'|','~')
FROM         CLAIM_TAB
WHERE     (ACCT_NO LIKE '%|')
order by acct_no
I got:
Account Number
001-4017048~
03028005133321~
0501347~CL2947.65~
1230117148~TOPAZ~
1343042~/SC~1355947~
1520170380~
Now the question, it could be very simple, but I am lost at this moment (APRECIATED!!!)
Where do I stick the REPLACE statement in my DTS JOB to extract making the change to those records?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Try first replacing the doubled bars with something (like your tilde) not used in the data. Then replace the single bars with two. Finally replace the tilde with two bars.

declare @C varchar(40)

set @C = 'my|data||with||bars|in|it'

select replace(replace(replace(@c,'||','~'),'|','||'),'~','||')

--Jonathan

cmartinz SSC Eights! Points: 941 More actions · Answer 2

thank you Jonathan. As you can see in the sample I posted, that is what I did, but using query analyzer.

My question was, and I am sorry if I did not made myself clear enough, when I run the DTS step in which this changes has to be done, where do I stick the REPLACE statement.

Let's say I have a DTS with a simple select.

SELECT fname, lname, account_no from claim_tab

running this simple select in the DTS I got all what I need, including those accounts with the | at the end.

Let's say the records returned are ten. Out of those ten, I have two with the | at the end. So, I need to replace the | with my ~

Where do I stick the REPLACE? I cannot do it afterwards due to the Double pipe delimitrer requested by the program loading the data in the other database. So, I need to do it on the run.

I hope this explains a little bit more my dilema. Thank you

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

In your select statement you can use case when ACCT_NO LIKE '%|' then REPLACE(ACCT_NO,'|','~') else ACCT_NO end.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

oc2viking Grasshopper Points: 24 More actions · Answer 4

How about running the data from the select statement into a temp table. Make the needed data changes there and then create the file from the temp table.

guarddata SSCertifiable Points: 6583 More actions · Answer 5

You could just create a view that does the replace commands and run your DTS job using the view.

Guarddata-

cmartinz SSC Eights! Points: 941 More actions · Answer 6

Thanks to all. I will test the CASE option as well as the view. The temp table could be an option later. I created a store proc this morning and will test that one too. Thank you again.