How to Replace a String in a SQL Server Table Column

How to replace a string in a SQL Server Table Column

It's this easy:

update my_table
set path = replace(path, 'oldstring', 'newstring')

UPDATE and REPLACE part of a string

You don't need wildcards in the REPLACE - it just finds the string you enter for the second argument, so the following should work:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

If the column to replace is type text or ntext you need to cast it to nvarchar

UPDATE dbo.xxx
SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '')
WHERE ID <=4

SQL Server replace string based on another table

Demo on dbfiddle

2 steps:

1. Find all the words to replace.
2. Replace with each @OldValue to @NewValue word accordingly.

CREATE FUNCTION [dbo].[TranslateString]
(
 @Str nvarchar(max)
)RETURNS nvarchar(max)
AS
BEGIN
  DECLARE @OldValue nvarchar(100);
  DECLARE @NewValue nvarchar(100);
  DECLARE @CHARINDEX INT = 0;
  DECLARE @Result nvarchar(100) = @Str;
  DECLARE @TempTable AS TABLE(OldValue varchar(100), NewValue varchar(100), isApply BIT) 

  --1. Region: Find all the words to replace
   WHILE (@CHARINDEX < LEN(@Str))
   BEGIN
     SELECT TOP 1 @OldValue = OldValue, @NewValue = newvalue, @CHARINDEX = CHARINDEX(oldvalue, @Str) 
      FROM LookupTab
        WHERE CHARINDEX(oldvalue, @Str) > @CHARINDEX
        ORDER BY CHARINDEX(oldvalue, @Str)

     IF(ISNULL(@OldValue, '') != '' AND NOT EXISTS(SELECT TOP 1 1 FROM @TempTable WHERE OldValue = @OldValue))   
         INSERT INTO @TempTable(OldValue, NewValue)
         VALUES(@OldValue, @NewValue)

     SET @CHARINDEX = @CHARINDEX + LEN(@OldValue);
   END
 --1. End-Region: Find all the words to replace

  --2. Region: Replace with each @OldValue to @NewValue word accordingly
  WHILE(EXISTS(SELECT OldValue FROM @TempTable WHERE ISNULL(isApply, 0) = 0))
  BEGIN
       SELECT @OldValue = OldValue, @NewValue = NewValue FROM @TempTable WHERE ISNULL(isApply, 0) = 0

       SET @Result = replace(@Result,@Oldvalue,@NewValue);
       UPDATE @TempTable SET isApply = 1 WHERE OldValue = @OldValue
  END
  --2. End-Region: Replace with each @OldValue to @NewValue word accordingly

  RETURN @Result;
END

Output

Updated 2020-01-20

A new solution to fix some exceptional cases. Demo in db<>fiddle

• Create a strSplit function to be able to split each word into a table
• Replace each word by ISNULL(l.newvalue, s.val)
• Join all word after replacing into @Result then return.

    CREATE FUNCTION [dbo].[TranslateString]
    (
     @Str nvarchar(max)
    )RETURNS nvarchar(max)
    AS
    BEGIN
        DECLARE @Result NVARCHAR(MAX)
        ;WITH cte_TempTable AS(
           select ISNULL(l.newvalue, s.val) AS Value 
           from strSplit(@Str, ' ') s
           left join LookupTab l on s.val = l.oldvalue
        )
        SELECT @Result = (SELECT Value + ' ' FROM cte_TempTable FOR XML PATH(''))

      RETURN @Result;
    END

Output

Replace a string in a column with zero's

this one will replace string starts with 'WAIT'\

UPDATE TABLE_NAME SET COLUMNNAME = 0
WHERE COLUMNNAME LIKE 'WAIT%'

or
this one will check and replace string contains 'WAIT' anywhere

UPDATE TABLE_NAME SET COLUMNNAME = 0
WHERE COLUMNNAME LIKE '%WAIT%'

before update please check the result set is correct or not by using below select query

select COLUMNNAME from TABLE_NAME WHERE COLUMNNAME LIKE 'WAIT%'
select COLUMNNAME from TABLE_NAME WHERE COLUMNNAME LIKE '%WAIT%'

Replacing values in a string from another table without nested REPLACE

One alternative would be successively CROSS APPLY the replacements. Is this better? Idk. It performs the calculation in the FROM clause and cleans up the SELECT list. It returns the correct output as well

drop table if exists #Table1;
go
CREATE TABLE #Table1 (StringtoReplace NVARCHAR(200));

drop table if exists #Table2;
go
CREATE TABLE #Table2 (Greeting NVARCHAR(30), FName NVARCHAR(30), LName NVARCHAR(30));

INSERT INTO #Table1 VALUES ('[greeting] [fname] [lname] Here is the message');

INSERT INTO #Table2 
VALUES ('Hello','John','Smith'),
       ('Hi','Fred','Jones'),
       ('Howdy','Sue','Brown');

SELECT v3.StringtoReplace UpdatedString
FROM #Table1 t1
     cross apply #Table2 t2
     cross apply (values (replace(t1.StringToReplace, '[greeting]', t2.Greeting))) v1(StringtoReplace)
     cross apply (values (replace(v1.StringToReplace, '[fname]', [fname]))) v2(StringtoReplace)
     cross apply (values (replace(v2.StringToReplace, '[lname]', [lname]))) v3(StringtoReplace);

UpdatedString
Hello John Smith Here is the message
Hi Fred Jones Here is the message
Howdy Sue Brown Here is the message

If instead of temp tables the rows were stored in physical tables, then you could create a reusable inline table valued function, itvf. Using the itvf the query is only 3 lines of code and it produces the same output as before.

drop table if exists dbo.test_Table1;
go
CREATE TABLE dbo.test_Table1 (StringtoReplace NVARCHAR(200));

drop table if exists dbo.test_Table2;
go
CREATE TABLE dbo.test_Table2 (Greeting NVARCHAR(30), FName NVARCHAR(30), LName NVARCHAR(30));

INSERT INTO dbo.test_Table1 VALUES ('[greeting] [fname] [lname] Here is the message');

INSERT INTO dbo.test_Table2 
VALUES ('Hello','John','Smith'),
       ('Hi','Fred','Jones'),
       ('Howdy','Sue','Brown');

drop function if exists dbo.test_fnReplace;
go
create function dbo.test_fnReplace(
  @StringtoReplace              nvarchar(200))
returns table as return
select v3.StringtoReplace UpdatedString
from dbo.test_Table2 t2
     cross apply (values (replace(@StringtoReplace, '[greeting]', t2.Greeting))) v1(StringtoReplace)
     cross apply (values (replace(v1.StringToReplace, '[fname]', [fname]))) v2(StringtoReplace)
     cross apply (values (replace(v2.StringToReplace, '[lname]', [lname]))) v3(StringtoReplace);
go

select fnRep.UpdatedString
from dbo.test_Table1 t1
     cross apply dbo.test_fnReplace(t1.StringtoReplace) fnRep;

How to replace substring in a column using lookup table in sql server

If the performance really matters you need to materialized the data in advanced. This can be done creating separate table and using a trigger or modifying the routine that is populating the original table. If you are records are not inserted/updated on batches you will not harm the CRUD execution time.

You can easy create a good looking short T-SQL statement for building dynamic code for performing 6K updates, so you can give this a shot, too - don't use LIKE or complex conditions - just simple UPDATE-REPLACE statements for each lookup value.

In some cases, I am using SQL CLR functions for such replaces. For example:

DECLARE @Main TABLE
(
    [id] TINYINT
   ,[body] NVARCHAR(MAX)
   ,[subject] NVARCHAR(MAX)
);

DECLARE @Lookup TABLE
(
    [id] TINYINT -- you can use row_number to order
   ,[char] NVARCHAR(32)
   ,[value] NVARCHAR(32)
);

INSERT INTO @Main ([id], [body], [subject])
VALUES (1, ' Hi No ', ' Hi No ')
      ,(2, '  data', ' ');

INSERT INTO @Lookup ([id], [char], [value])
VALUES (1, '', N'だ')
      ,(2, '', N'づ');

DECLARE @Pattern NVARCHAR(MAX)
       ,@Replacement NVARCHAR(MAX);

SELECT @Pattern = [dbo].[ConcatenateWithOrderAndDelimiter] ([id], [char], '|')
      ,@Replacement = [dbo].[ConcatenateWithOrderAndDelimiter] ([id], [value], '|')
FROM @Lookup;


UPDATE @Main
SET [body] = [dbo].[fn_Utils_ReplaceStrings] ([body], @Pattern, @Replacement, '|')
   ,[subject] = [dbo].[fn_Utils_ReplaceStrings] ([subject], @Pattern, @Replacement, '|');

 SELECT [id]
       ,[body]
       ,[subject]
 FROM @Main;        

I am showing you the code behind below, but this is just an idea. You are free to implement something on your own, which satisfy your performance requirements.

Here, you can see how SQL CLR function is created. Here, is a variant of aggregate function concatenating with order:

[Serializable]
[
    Microsoft.SqlServer.Server.SqlUserDefinedAggregate
    (
        Microsoft.SqlServer.Server.Format.UserDefined,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = false,
        IsInvariantToOrder = false,
        IsNullIfEmpty = false,
        MaxByteSize = -1
    )
]
/// 

/// Concatenates  values defining order using the specified number and using the given delimiter
/// 

public class ConcatenateWithOrderAndDelimiter : Microsoft.SqlServer.Server.IBinarySerialize
{
    private List> intermediateResult;
    private string delimiter;
    private bool isDelimiterNotDefined;

    public void Init()
    {
        this.delimiter = ",";
        this.isDelimiterNotDefined = true;
        this.intermediateResult = new List>();
    }

    public void Accumulate(SqlInt32 position, SqlString text, SqlString delimiter)
    {
        if (this.isDelimiterNotDefined)
        {
            this.delimiter = delimiter.IsNull ? "," : delimiter.Value;
            this.isDelimiterNotDefined = false;
        }

        if (!(position.IsNull || text.IsNull))
        {
            this.intermediateResult.Add(new Tuple(position.Value, text.Value));
        }
    }

    public void Merge(ConcatenateWithOrderAndDelimiter other)
    {
        this.intermediateResult.AddRange(other.intermediateResult);
    }

    public SqlString Terminate()
    {
        this.intermediateResult.Sort();
        return new SqlString(String.Join(this.delimiter, this.intermediateResult.Select(tuple => tuple.Item2)));
    }

    public void Read(BinaryReader r)
    {
        if (r == null) throw new ArgumentNullException("r");

        int count = r.ReadInt32();
        this.intermediateResult = new List>(count);

        for (int i = 0; i < count; i++)
        {
            this.intermediateResult.Add(new Tuple(r.ReadInt32(), r.ReadString()));
        }

        this.delimiter = r.ReadString();
    }

    public void Write(BinaryWriter w)
    {
        if (w == null) throw new ArgumentNullException("w");

        w.Write(this.intermediateResult.Count);
        foreach (Tuple record in this.intermediateResult)
        {
            w.Write(record.Item1);
            w.Write(record.Item2);
        }
        w.Write(this.delimiter);
    }
}

Here is one variant of function performing replacement:

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString ReplaceStrings( SqlString input, SqlString pattern, SqlString replacement, SqlString separator ){
    string output = null;
    if(
        input.IsNull == false
        && pattern.IsNull == false
        && replacement.IsNull == false
    ){
        StringBuilder tempBuilder = new StringBuilder( input.Value );

        if( separator.IsNull || String.IsNullOrEmpty( separator.Value ) ){
            tempBuilder.Replace( pattern.Value, replacement.Value );
        }
        else{
            //both must have the exact number of elements
            string[] vals = pattern.Value.Split( new[]{separator.Value}, StringSplitOptions.None ),
                newVals = replacement.Value.Split( new[]{separator.Value}, StringSplitOptions.None );

            for( int index = 0, count = vals.Length; index < count; index++ ){
                tempBuilder.Replace( vals[ index ], newVals[ index ] );
            }
        }

        output = tempBuilder.ToString();
    }

    return output;
}

or this one but using regex:

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, Name = "RegexReplaceStrings")]
public static SqlString ReplaceStrings(SqlString sqlInput, SqlString sqlPattern, SqlString sqlReplacement, SqlString sqlSeparator)
{
    string returnValue = "";

    // if any of the input parameters is "NULL" no replacement is performed at all
    if (sqlInput.IsNull || sqlPattern.IsNull || sqlReplacement.IsNull || sqlSeparator.IsNull)
    {
        returnValue = sqlInput.Value;
    }
    else
    {
        string[] patterns = sqlPattern.Value.Split(new string[] {sqlSeparator.Value}, StringSplitOptions.None);
        string[] replacements = sqlReplacement.Value.Split(new string[] { sqlSeparator.Value }, StringSplitOptions.None);

        var map = new Dictionary();

        // The map structure is populated with all values from the "patterns" array as if no corresponding value exists
        // in the "replacements" array the current value from the "pattern" array is used a a replacement value. The
        // result is no replacement is done in the "sqlInput" string if the given "pattern" is matched.
        for (int index = 0; index < patterns.Length; index++)
        {
            map[patterns[index]] = index < replacements.Length ? replacements[index] : patterns[index];
        }

        returnValue = Regex.Replace(sqlInput.Value, String.Join("|", patterns.Select(patern => Regex.Replace(patern, @"\(|\)|\||\.", @"\$&")).OrderByDescending(patern => patern.Length).ToArray()), match =>
        {
            string currentValue;

            if (!map.TryGetValue(match.Value, out currentValue))
            {
                currentValue = match.Value;
            }

            return currentValue; 
        });
    }

    return new SqlString(returnValue);
}

Replace string from column with NULL in sql

You need to update the table:

UPDATE df
SET Col1 = NULL
WHERE Col1 = '---';

As for ad-hoc query:

select replace(Col1, '---', NULL) from df
<->
select nullif(col1, '---') AS col1 from df;

SQL replace - find word and replace it

you need the text column in replace

Update TABLE
SET Text = replace(text, 'TEAM', 'AMOUNT')
Where text like '%TEAM%'

seems you are not using mysql but sql.serve and you have ntext

so you could try using a cast

Update TABLE
SET Text = replace(cast text  as nvarchar(4000), 'TEAM', 'AMOUNT')
Where text like '%TEAM%'

---

Related Topics