none
search and replace string in SP RRS feed

  • Question

  • Hello

    Does anyone have an example on a search and replace in a stored procedure?

    I have a table where the sub text string in a field (TechnicalAddress) must be searched and replaced.

    e.g

    VISTA_1_2-1_Kvadrant-DTU_NET_1-B105_G01-B105_UC2_DUC46-ALS1.EK60_A
    VISTA_1_2-1_Kvadrant-DTU_NET_1-B105_G01-B105_UC2_DUC46-ALS1.EK61_A
    VISTA_1_2-1_Kvadrant-DTU_NET_1-B105_G01-B105_UC2_DUC46-ALS1.EK62_A
    VISTA_1_2-1_Kvadrant-DTU_NET_1-B105_G01-B105_UC2_DUC46-ALS1.EK63_A

     

    VISTA_1_2-1_Kvadrant-DTU_NET_1-B110_G01-B105_UC2_DUC46-ALS1.EK60_A
    VISTA_1_2-1_Kvadrant-DTU_NET_1-B110_G01-B105_UC2_DUC46-ALS1.EK61_A

    I would like to
    replace
    VISTA_1_2-1_Kvadrant

    with this
    SUBNET1-AREA2

    the rest must stay unchanged

    I'm using SQL server 2005


    Henry

    Thursday, August 12, 2010 11:20 AM

Answers

  • Hello Henry,

    You can use an UPDATE statement like below

    UPDATE TABLE TableName
    SET
    	TechnicalAddress = REPLACE(TechnicalAddress, 'VISTA_1_2-1_Kvadrant', 'SUBNET1-AREA2')
    
    

    Test it before using on productive sites

    I hope that helps,

     


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    • Proposed as answer by Kent WaldropModerator Thursday, August 12, 2010 11:48 AM
    • Marked as answer by Henry64 Thursday, August 12, 2010 6:38 PM
    Thursday, August 12, 2010 11:29 AM
    Moderator

All replies

  • Hello Henry,

    You can use an UPDATE statement like below

    UPDATE TABLE TableName
    SET
    	TechnicalAddress = REPLACE(TechnicalAddress, 'VISTA_1_2-1_Kvadrant', 'SUBNET1-AREA2')
    
    

    Test it before using on productive sites

    I hope that helps,

     


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    • Proposed as answer by Kent WaldropModerator Thursday, August 12, 2010 11:48 AM
    • Marked as answer by Henry64 Thursday, August 12, 2010 6:38 PM
    Thursday, August 12, 2010 11:29 AM
    Moderator
  • declare @var varchar(100)
    set @var = 'VISTA_1_2-1_Kvadrant-DTU_NET_1-B110_G01-B105_UC2_DUC46-ALS1.EK60_A'

    select @var, REPLACE(@var,'VISTA_1_2-1_Kvadrant','SUBNET1-AREA2')

    Thursday, August 12, 2010 11:31 AM
  • Solved like this - thanks guys

    ALTER PROC [dbo].[SP_FindANDReplaceString]
    (
    	@Tablename nvarchar(50),
    	@FieldName nvarchar(128),
    	@FindString nvarchar(255),
    	@ReplaceString nvarchar(255),
    	@WhereString nvarchar(255)
    )
    AS
    	DECLARE @sqlstring varchar(8000)
    
    select @sqlstring = 'UPDATE ' + @Tablename + ' SET [' + @FieldName + '] = REPLACE([' + @FieldName + '],''' + @FindString + ''',''' + @ReplaceString +''') '
    if @WhereString is not null SET @sqlstring = @sqlstring + ' WHERE ' +@WhereString
    
    execute (@sqlstring)
    

     


    Henry
    Thursday, August 12, 2010 6:34 PM
  • Try now this code with the single quote inside either FindString or ReplaceString.

    Why do you think the dynamic SQL is necessary for such task? And if you want to use dynamic SQL, at least use @FindString and @ReplaceString as parameters by using sp_ExecuteSQL instead of execute(@SQL)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, August 12, 2010 6:38 PM
    Moderator