SQL 2017: Translate over Replace
Author: jason
Date: 2019-01-17 11:23:20
Category: Technical

In SQL 2017 a more powerful TRANSLATE command can be used instead of REPLACE. What would take multiple REPLACE instances, can be done with one. There can be only one

https://docs.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-2017

Code


/*
TRANSLATE instead of REPLACE
*/

DROP TABLE IF EXISTS #Temp;
CREATE TABLE #Temp ([Name] VARCHAR(25), [Description] VARCHAR(255), Quantity INT, SalesDate VARCHAR(10));
INSERT #Temp VALUES ('Apple','Red', 10, '1^1^2019'), ('Apple','Green', NULL, '1-2-2019'), ('Plum','Purple', 5, '01&03&2019');

SELECT REPLACE(REPLACE(REPLACE(SalesDate, '^', '/'), '-', '/'), '&', '/') AS OldMulti FROM #Temp;
SELECT TRANSLATE(SalesDate, '^-&','///') AS NewShiny FROM #Temp;








jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009