I’m working through some issues with the editor. Please forgive the formating, if it is hard to read. I also am unable to link to the previous editorial introducing this SQL Puzzler. It should show on the site with my list of editorials.
Regardless, today I’m sharing the simple answer to the problem. Just use a cursor. There are more elegant answers not using a cursor. But, this demonstrates one classic way to solve the problem, allowing you to appreciate the newer options.
Here is a quick summary of what we are trying to do. I have a table called #Strings with a column sometext. Sometext has a four rows, each with a phrase. For each phrase I want to replace matching wordswith an abbreviation, as found in the table #Abbrecviation. #Abbreviation contans a set of words I want to find, and an assocaited abbreviation as a replacment for the found word;
Here is a cursor based solution that works.
DECLARE @CONTINUE CHAR(1) = 'Y'
DECLARE @SearchText VARCHAR(128)
DECLARE @Abbreviation VARCHAR(10)
DECLARE C CURSOR FOR SELECT * FROM #Abbreviations
OPEN C
BEGIN TRAN
WHILE @CONTINUE = 'Y'
BEGIN
FETCH NEXT FROM C INTO @SearchText, @Abbreviation
IF @@FETCH_STATUS = 0
BEGIN
UPDATE #Strings SET sometext=REPLACE(Sometext, @SearchText, @Abbreviation)
END
ELSE
BEGIN
SET @CONTINUE = 'F'
END
END
CLOSE C
DEALLOCATE C
SELECT *
FROM #Strings
ROLLBACK
Text Before
Mst and Slv Regulator
Hdr and Ftr Cylinder
Rt Side Lever
Lft Side Lever
Text After
Mst and Slv Regulator
Hdr and Ftr Cyl
Rt Side Lever
Lft Side Lever
There are other ways to do this without a cursor. We’ll get back to that next week. In the mean time, feel free to share your solution in comments or drop me an Email at btaylor@sswug.org.
Cheers
Ben