REPLACE function help!

  • 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:


    FROM         CLAIM_TAB

    WHERE     (ACCT_NO LIKE '%|')

    I got:

    Account Number







    Then I use query analyzer to replace the single pipe      


    FROM         CLAIM_TAB

    WHERE     (ACCT_NO LIKE '%|')

    order by acct_no

    I got:

    Account Number







    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?

  • 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,'||','~'),'|','||'),'~','||')


  • 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

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



    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

  • 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.

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


  • 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.

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

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