Post reply

How To Replace Part Of A Text Column

  • September 8, 2009 at 2:00 pm

    #141499

    This is a followup to a question I had last week about updating a text column. Here I want to replace part of the string. More specifically, I want to add a single space after the '%F%,'

    'usp_CheckWeeklyResponseTeam %BD%, %ED%, %S%, %F%,%D%, %V%'

    Giving:

    usp_CheckWeeklyResponseTeam %BD%, %ED%, %S%, %F%, %D%, %V%

    I'm using the following test scripts. With either script, no space gets added.

    Either way, the code works on any search string that does not contain the % (wildcard) as part of the text. When I include the % as part of what I need to search for, it no longer works.

    Using [ ] to add the space.

    DECLARE @SQLTEXT Table(SQLText ntext)

    INSERT INTO @SQLTEXT Values('usp_CheckWeeklyResponseTeam %BD%, %ED%, %S%, %F%,%D%, %V%')

    Select SQLTEXT , REPLACE(CONVERT(varchar(max),SQLTEXT),'%[%]F[%],[%]D[%]%','%[%]F[%],[ ] [%]D[%]%')

    From @SQLTEXT

    Using '' '' to add the space.

    DECLARE @SQLTEXT Table(SQLText ntext)

    INSERT INTO @SQLTEXT Values('usp_CheckWeeklyResponseTeam %BD%, %ED%, %S%, %F%,%D%, %V%')

    Update @SQLTEXT

    Set SQLText = REPLACE(CONVERT(varchar(max),SQLText),'%[%]F[%],[%]D[%]%','%[%]F[%],'' '' [%]D[%]%')

    Select * from @SQLTEXT

    Thanks.

  • September 8, 2009 at 2:38 pm

    #1050797

    This worked out fine for me.

    DECLARE @SQLTEXT Table(SQLText ntext)

    INSERT INTO @SQLTEXT Values('usp_CheckWeeklyResponseTeam %BD%, %ED%, %S%, %F%,%D%, %V%')

    Select SQLTEXT , REPLACE(CONVERT(varchar(max),SQLTEXT),'%F%,%D%','%F%, %D%')

    From @SQLTEXT

  • September 8, 2009 at 2:49 pm

    #1050806

    Or,

    If you are not sure where you need the space you could search for ',%'

    DECLARE @SQLTEXT Table(SQLText ntext)

    INSERT INTO @SQLTEXT Values('usp_CheckWeeklyResponseTeam %BD%, %ED%, %S%, %F%,%D%, %V%')

    Select SQLTEXT , REPLACE(CONVERT(varchar(max),SQLTEXT),',%',', %')

    From @SQLTEXT

  • September 8, 2009 at 3:05 pm

    #1050811

    I knew that....:blush:..Thanks a bunch!

  • September 8, 2009 at 7:14 pm

    #1050865

    Consider moving away from NTEXT if you can:

    Books Online

    ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    See the .WRITE clause of the UPDATE statement too.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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