-- create simplified table for example
CREATE TABLE dbo.ref_RoadTypes 
( 
     RoadTypeAbbr VARCHAR(20) NOT NULL 
     , RoadType VARCHAR(50) NOT NULL 
) 


-- populate table
SET NOCOUNT ON
DECLARE @ErrorID INT
BEGIN TRANSACTION
INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('ALY'), 'Alley'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('ARC'), 'Arcade'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('AVE'), 'Avenue'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BCH'), 'Beach'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BG'), 'Burg'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BGS'), 'Burgs'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BLF'), 'Bluff'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BLVD'), 'Boulevard'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BND'), 'Bend'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BR'), 'Branch'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BRG'), 'Bridge'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BRK'), 'Brook'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BRKS'), 'Brooks'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BTM'), 'Bottom'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BYP'), 'Bypass'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('BYU'), 'Bayou'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CIR'), 'Circle'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CIRS'), 'Circles'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CLB'), 'Club'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CLF'), 'Cliff'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CLFS'), 'Cliffs'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CMN'), 'Common'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('COR'), 'Corner'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CORS'), 'Corners'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CP'), 'Camp'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CPE'), 'Cape'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CRES'), 'Crescent'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CRK'), 'Creek'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CRSE'), 'Course'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CRST'), 'Crest'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CSWY'), 'Causeway'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CT'), 'Court'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CTR'), 'Center'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CTRS'), 'Centers'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CTS'), 'Courts'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CURV'), 'Curve'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CV'), 'Cove'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CVS'), 'Coves'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('CYN'), 'Canyon'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('DL'), 'Dale'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('DM'), 'Dam'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('LN'), 'Lane'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('RD'), 'Road'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('ST'), 'Street'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('TERR'), 'Terrace'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

INSERT INTO dbo.ref_RoadTypes(RoadTypeAbbr,RoadType) SELECT UPPER('XING'), 'Crossing'
SELECT @ErrorID = @@ERROR
IF @ErrorID != 0 GOTO ENDOFSCRIPT

ENDOFSCRIPT:
IF @ErrorID != 0
BEGIN
	IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
END
ELSE
BEGIN
	IF @@TRANCOUNT != 0 COMMIT TRANSACTION
	PRINT 'Script executed successfully'
END
SET NOCOUNT OFF


-- See records that were created
SELECT * FROM dbo.ref_RoadTypes


-- SOUNDEX test
DECLARE @Suffix VARCHAR(20)
SELECT @Suffix = 'Bolevard' --(intentionally misspelled)
SELECT RoadTypeAbbr FROM dbo.ref_RoadTypes
WHERE SOUNDEX(RoadType) = SOUNDEX(@Suffix)


-- DIFFERENCE test
DECLARE @Phrase VARCHAR(20)
SELECT @Phrase = 'Str'
SELECT TOP 1 RoadTypeAbbr FROM dbo.ref_RoadTypes
ORDER BY DIFFERENCE(@Phrase, RoadType) DESC