Here’s a little SQL Challenge for those of you who like to do SET logic instead of procedural loops, etc.
I have an SQL Query that doesn’t behave the way I thought it should. I have a table containing a list of phrases. I want to modify the table of phrases, replacing each word in the phrase with an abbreviation of the word. Each phrase would be modified as many times as there are words in the phrase, with abbreviations asigned. Here are some sample tables demonstrating the problem.
Create Table #Strings (
sometext varchar(128) NOT NULL
)
GO
INSERT INTO #Strings
Values
('Master and Slave Regulator')
,('Header and Footer Cylinder')
,('Right Side Lever')
,('Left Side Lever')
GO
Create table #Abbreviations (
SearchValue VARCHAR(128) NOT NULL
,Abbreviation VARCHAR(10) NOT NULL
,CONSTRAINT PKAbbrev PRIMARY KEY CLUSTERED (SearchValue)
)
GO
INSERT INTO #Abbreviations
VALUES
('Master', 'Mst')
,('Slave', 'Slv')
,(‘Regulator’, ‘Reg’)
,('Header', 'Hdr')
,('Footer', 'Ftr')
,('Cylinder', 'Cyl')
,('Right', 'Rt')
,('Left', 'Lft')
,('Level', 'Lvr')
,(‘Light’, ‘lt’)
GO
As you may have guessed, I want the String table to be replaced with the following phrases:
Mst Slv Reg
Hdr Ftr Cyl
Rt Side Lvr
Lt Side Lvr
Here’s the query I tried at first.
BEGIN TRAN
UPDATE S SET
S.sometext = REPLACE(s.SomeText, a.SearchValue, a.Abbreviation)
FROM #Abbreviations a
CROSS JOIN #Strings s
SELECT * FROM #Strings
ROLLBACK
Here’s the results after my query:
Master and Slave Regulator
Header and Footer Cyl
Right Side Lever
Left Side Lever
As you can see, it is only applying the first abbreviation from the abbreviation table. Cylinder is the first because of the clustered index on the #Abbreviations table. Because the query uses a cross join, every abbreviation in the #Abbreviation table should be applied to every row in the String table. This is not the case. Turning ROWCOUNT on, there are only four rows being modified.
Can you figure out how to make this work? Leave a comment, or drop an Email to btaylor@sswug.org. I’ll post a solution tomorrow.
Cheers,
Ben