Post reply

Replace string within a blob file

  • December 21, 2004 at 1:25 pm

    #76216

    I have a blob file in SQL Server 2000 where I need to find all instances of  ':' and replace with '--'.  I tried the following SQL statement:

    select replace(GDTXVC, ':', '-') from proddta.f00165 where gdtxky= '2514|SO|00001|1.000'

    But,  received this error message: Argument data type text is invalid for argument 1 of replace function.

    Is there a special statement which will work on this field for a replace command? 

    Thanks!

    Heather

     

  • December 21, 2004 at 3:25 pm

    #534689

    Hi there,

    I'm not sure I understand exactly what your table looks like.  Is the blob implemented as a "text" datatype?  If so then you might look in BOL for UPDATETEXT and TEXTPTR.  Another possibility is to pull the data out to a varchar(8000) work area with READTEXT, modify the data using replace, and write it back to the text column with WRITETEXT.  Basically write your own clone for the replace function.  I am not aware of a direct equivalent for text columns, which is not to say that there isn't one.

    Hope this helps

    Wayne

  • December 21, 2004 at 7:22 pm

    #534704

      Thanks for pointing me in the right direction.  I found a script that I was able to edit which utilized the UPDATETEXT, TEXTPTR, and Cursor functions to locate the position where the character to be replaced was stored. 

  • July 18, 2005 at 11:37 am

    #575270

    Please send to script to help others who may wish to utilize it

    Brett

Viewing 4 posts - 1 through 3 (of 3 total)

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