Search and replace entire column value SQL Server

Question

My first time at Stack Overflow. I need help creating a procedure that searches for a specific string and replaces the entire value of that column in the database to another string. for example here is the data i need to search for and replace it with:

search column:         replace column:
1.'rbc inv',           1.'RBC dominion securities',
2.'rbc dom',           2.'RBC dominion securities',
3.'RBC'                3.'RBC Bank'

I receive the above in Excel format and I upload it as a table into SQL Server then run my script as you can see below. there are column that currently have the string RBC investment for Seniors' that needs to be replaced with 'RBC dominion securities (number 1 above).

My issue is that the replace function doesn't work when the search columns are very similar to each other as you can see above and it only replaces the string found and not the entire cell or column value in the database. I tried putting a line to set the column value to null before it replaces it with the new value and failed multiple times. I have a way to do this manually one at a time but when I receive an excel sheet with over 300 columns that I need to look for and replace is very frustrating. Can anyone help? Please and thank you.

Here is the script I created.

SET NOCOUNT ON;

DECLARE @searchColumnValue nvarchar(max), @replaceColumnValue nvarchar(max)

DECLARE replace_cursor CURSOR FOR 
SELECT searchColumn, replaceColumn             -
FROM dbo.search

OPEN replace_cursor

FETCH NEXT FROM replace_cursor
INTO @searchColumnValue , @replaceColumnValue 
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE contact3                          
    SET notes = REPLACE(notes, @searchColumnValue, @replaceColumnValue)  

    FETCH NEXT FROM replace_cursor
    INTO @searchColumnValue , @replaceColumnValue
END 
CLOSE replace_cursor
DEALLOCATE replace_cursor

Is the search string some text that you need to look for in column data? and if you found it do you want to replace just that search string with another or do you want to replace all data in that column with new string? — DhruvJoshi
– DhruvJoshi, Commented Mar 9, 2016 at 19:45
Are you saying that you want your UPDATE to replace rbc inv but not replace rbc investments? Is that the only problem? — Tab Alleman
– Tab Alleman, Commented Mar 9, 2016 at 20:00
@Karmran :I am using a cursor to iterate between the Excel table i uploaded and loop through each search column . I am looking to replace the entire and all column with a new string . — Maz Baz
– Maz Baz, Commented Mar 9, 2016 at 20:27
@ Tab Alleman : I want my search to look for any sentence that may containt 'RBC inv' in it. so for example "RbC investment for Seniors 65+' and 'RBC inv for juniors' both need to be replaced to 'RBC dominion securities' — Maz Baz
– Maz Baz, Commented Mar 9, 2016 at 20:32

Philipp · Accepted Answer · 2016-03-09 22:22:43Z

1

I hope I understood it correctly. So I'll repeat.

You have 1 table with lots of entries
You have this list from excel where you search for the "search column"
In case of a match, replace the whole value with "replace column"

If this is the case, than this might be the solution:

declare @data table (Column1 nvarchar(50))

insert  into @data
        (Column1)
values  (N'RbC investment for Seniors 65+'),
        (N'RBC inv for juniors')

declare @replace table
    (
     OriginalValue nvarchar(50),
     NewValue nvarchar(50),
     [priority] int
    )

insert  into @replace
        (OriginalValue, NewValue, [priority])
values  (N'rbc inv', N'RBC dominion securities', 2),
        (N'rbc dom', N'RBC dominion securities', 2),
        (N'RBC', N'RBC Bank', 3)

update  @data
set     Column1 = coalesce((
                            select top 1
                                    NewValue
                            from    @replace
                            where   Column1 like '%' + OriginalValue + '%'
                            order by [priority]
                           ), Column1)

select  *
from    @data

The table "data" would be the one where you do the replacement.

There can be quite some side effects using that (e.g. wildcards like % in "search_column", maybe multiple matches - right now a "random" one is then taken, the performance might not be the best, ...) But I guess for a more precise answer I would need a better question.

Edit:

Thanks to Ralph... I added a priority to the "replace" table to be able to handle duplicate matches.

In case "RBC" has priority 3 the result is:

With a priority of 1 it is:

edited Mar 9, 2016 at 22:22

answered Mar 9, 2016 at 21:29

Philipp

1,4451 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ralph Over a year ago

You might want to expand upon your solution and mention the importance of the sort order in the replacement table. If replace N'RBC' for N'RBC Bank' would be further up in the replacement table then this would get (incorrectly) substituted first.

Philipp Over a year ago

Hi Ralph, thanks for the update of the query. Is of course much better to be able to test it right away :-)

Maz Baz Over a year ago

Thank you to both Philipp and Ralph. you guys seriously just saved my life. Take a bow. Hands off to you.

Collectives™ on Stack Overflow

Search and replace entire column value SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related