Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Posts
    26

    Unanswered: Replace on a text field.

    I need to search for all occurances of particular string within a column on a table. The column has a data type of Text. It will not allow me to use the replace function on a Text field only on varchars or chars. Does anybody have any ideas of how I can do this?
    Nicky Jones

  2. #2
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    njjones,

    Look at Full Text Indexing in Books Online (BOL).

    If however you can guarantee that none of the fields exceed 8000 characters you could CAST the TEXT field to a VARCHAR(8000). ie.

    REPLACE(CAST(yourtextcolumn AS VARCHAR(8000)),'ABC','DEF')

    macka.

  3. #3
    Join Date
    Feb 2003
    Posts
    26

    Tried that

    I have done that however quite a lot of the fields I need to affect are greater than 8000 characters (hence using the text datatype). I wanted to run a query to find out how many were longer but you can't use Len on a text field either - is there an easy way of finding the character length of text in a text column?
    Nicky Jones

  4. #4
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17

    Re: Tried that

    Chances are not many will be exactly 8000 in length, so the following query gives you a rough idea of how many are bigger than 8k, but truncating all fields to 8000 characters.

    SELECT COUNT(*)
    FROM yourtable
    WHERE LEN((CAST(yourtextcolumn AS VARCHAR(8000)))) = 8000

    macka.

  5. #5
    Join Date
    Feb 2003
    Posts
    26

    Sorted it

    The following works - seems a little heavy handed for a replacement of a one line function, but any:

    declare datacursor cursor
    for
    select
    dataid, TEXTPTR(description)
    from
    tbl_data
    where
    description like '%25.224.8.30%'
    declare @ptrval binary(16)
    declare @dataid int
    declare @pos1 int
    open datacursor
    fetch next from datacursor
    into @dataid, @ptrval
    while @@fetch_status = 0
    begin
    select @pos1 = patindex('%25.224.8.30%',tbl_data.description) from
    tbl_data where dataid = @dataid
    while @pos1 <> 0
    begin
    set @pos1 = @pos1-1
    updatetext tbl_data.description @ptrval @pos1 11
    'modconnect1.qinetiq.r.mil.uk'
    select @pos1 =
    patindex('%25.224.8.30%',tbl_data.description) from tbl_data where dataid =
    @dataid
    end
    fetch next from datacursor
    into @dataid, @ptrval
    end
    close datacursor
    deallocate datacursor
    Nicky Jones

  6. #6
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question njjones

    njjones,

    Did you ever get the 'replace' issue resolved in a Text field? I need to do a similar action, finding all the commas in a text field and replacing it with a semi-colon.

    Thanks.

  7. #7
    Join Date
    Feb 2003
    Posts
    26
    The answer is above, however I have recopied and pasted it below and updated it so that it should work for , and ; - probably could have parameterised this and turned it in a user defined function but it is not something I have needed to do often enough to bother with:

    declare datacursor cursor
    for
    select
    dataid, TEXTPTR(description)
    from
    tbl_data
    where
    description like '%,%'
    declare @ptrval binary(16)
    declare @dataid int
    declare @pos1 int
    open datacursor
    fetch next from datacursor
    into @dataid, @ptrval
    while @@fetch_status = 0
    begin
    select @pos1 = patindex('%,%',tbl_data.description) from
    tbl_data where dataid = @dataid
    while @pos1 <> 0
    begin
    set @pos1 = @pos1-1
    updatetext tbl_data.description @ptrval @pos1 1
    ';'
    select @pos1 =
    patindex('%,%',tbl_data.description) from tbl_data where dataid =
    @dataid
    end
    fetch next from datacursor
    into @dataid, @ptrval
    end
    close datacursor
    deallocate datacursor
    Nicky Jones

  8. #8
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Thumbs up Thanks

    Nicky, thanks. A couple of quick modifications and I had this working well for my table. I appreciate it.

    RY

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •