Refactoring - Two Terrible Lines

Question

I worked on a stored procedure and hit the point where I have two really terrible lines. Is there a way to rewrite this more clearly in a stored procedure? If not, how would I like to create a function for this?

, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(l.tenant_trading_name,'~','-'), '"','-'), '#','-'), '%','-'), '*','-'), ':','-'), '<','-'), '>','-'), '?','-'), '/','-'), '\','-'), '{','-'), '|','-'), '}','-') as trading_name , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(l.suite_name,'&','-'), '~','-'), '"','-'), '#','-'), '%','-'), '*','-'), ':','-'), '<','-'), '>','-'), '?','-'), '/','-'), '\','-'), '{','-'), '|','-'), '}','-') as suite_name 

Answer 1

Well, you can just have a function that does the dirty work for you:

 CREATE FUNCTION dbo.CleanCharacters ( @InputString VARCHAR(64), @UseAmp BIT ) RETURNS VARCHAR(64) AS BEGIN RETURN(SELECT REPLACE(REPLACE(REPLACE(...REPLACE( @InputString, CASE WHEN @UseAmp = 1 THEN '&' ELSE '-' END, '-'), '~', '')...etc etc...))) ); END GO 

Then you can say:

 SELECT dbo.CleanCharacters(l.tenant_trading_name, 0), dbo.CleanCharacters(l.suite_name, 1) FROM ... 

This at least abstracts the ugly REPLACE() calls from the procedure.

(Note that I didn’t quite parse the entire line to see if there were any other differences, but it seemed to me that the only difference was that package_name could not have a name and a trade name.)

Another way is to keep your “bad” characters in the table, making it easier to maintain these replacements (and once the table is full, which will make the function much cleaner).

 CREATE TABLE dbo.DirtyCharacters(x CHAR(1)); INSERT dbo.DirtyCharacters SELECT '~' UNION ALL SELECT '&' UNION ALL SELECT '*' -- ... ; 

Now you can just say your function:

 ALTER FUNCTION dbo.CleanCharacters ( @InputString VARCHAR(64), @UseAmp BIT ) RETURNS VARCHAR(64) AS BEGIN SELECT @InputString = REPLACE(@InputString, x, '-') FROM dbo.DirtyCharacters WHERE x <> CASE WHEN @UseAmp = 1 THEN '' ELSE '&' END; RETURN (@InputString); END GO 

Answer 2

It seems to work for your first line, for example.

  SELECT @Str = REPLACE( @Str , chr , '-' ) FROM ( SELECT '~' UNION SELECT '"' UNION SELECT '#' UNION SELECT '%' UNION SELECT '*' UNION SELECT ':' UNION SELECT '<' UNION SELECT '>' UNION SELECT '?' UNION SELECT '/' UNION SELECT '\' UNION SELECT '{' UNION SELECT '|' UNION SELECT '}' UNION SELECT '%' ) D ( chr ) WHERE CHARINDEX( chr , @Str ) > 0 ; SELECT @str ;