Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2006
    Posts
    35

    Unanswered: Replace partial text string in sql text field

    I have a list of names in a text field in sql 2005. I need to replace names like "Anderson" with "Anderson, R." from a list that looks like this: "AndersonAtkinsBensonBrown..."

    How do I replace "Anderson" with "Anderson, R." without deleting the rest of the field?

    I'm so close to being done with this project - and now this!!!

    Thanks in advance...

  2. #2
    Join Date
    Feb 2007
    Posts
    38
    Hi

    Simple replace function will do the job as below.

    if object_id('tempdb..#customer_tbl') is not null
    drop table #customer_tbl

    create table #customer_tbl
    (
    id int identity(1,1)
    , namelist text
    )
    insert into #customer_tbl values('AndersonAtkinsBensonBrownTerryPackWilliamJ ay')
    insert into #customer_tbl values('BensonBrownTerryPackAndersonAtkinsWilliamJ ay')
    insert into #customer_tbl values('TerryPackWilliamJayBensonBrownAndersonAtki ns')

    SELECT * FROM #customer_tbl

    UPDATE a
    Set namelist=replace(cast(namelist as varchar(max)),'Anderson','Anderson, R.')
    FROM #customer_tbl a
    where namelist like '%Anderson%'

    SELECT * FROM #customer_tbl

  3. #3
    Join Date
    Dec 2006
    Posts
    35

    How about this?

    select cast(replace(cast(myntext as nvarchar(max)),'find','replace') as ntext)
    from myntexttable

    I guess I should convert text fields into varchar max?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure, but I think replace has a return value of varchar(8000).
    Code:
    select datalength (replace (replicate('a', 4000) + 'bbbb' + replicate('c', 3996), 'bb', '1234'))
    EDIT: OK. I am sure, now.

  5. #5
    Join Date
    Dec 2006
    Posts
    35

    I converted text field to varchar(max)

    Seems to be fine. I'm sure none of the fields exceed 8000.

    But now - how do I find and replace in varchar(max)?

    I need to replace tablename.varcharfield 'day' with 'day, d.' without changing any of the other text in the field. Is that possible?

    Thanks.

Tags for this Thread

Posting Permissions

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