mercredi 9 mars 2016

Search and replace entire column value SQL server

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: 1.'rbc inv',2.'rbc dom',3.'RBC' replace column: 1.'RBC dominion securities' 2.'RBC dominion securities' 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 to'RBC dominion securities (number 1 above). my issue is that 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

Aucun commentaire:

Enregistrer un commentaire