SQL Puzzle | The Replace Puzzle

The puzzle says that you have to find and replace a sub string with a new string. The catch here is that is the sub string should not be preceded with any alphabet.

E.g In ‘PortlandOR,OR,OR|or’, We want the replace “or” with “K”. Now only these “OR,OR|or” will be replaced with K. If the sub string is preceded or exceeded with any alphabet it should NOT be replaced.

Please check out the sample input values and expected output below.

Sample Input

‘PortlandOR,OR,OR|or’

Expected Output

PortlandOR,K,K|K

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Select and Numbers Table

 ``` -- DECLARE @InputString AS VARCHAR(1000) = 'PortlandOR,OR,OR|or' DECLARE @SubString AS VARCHAR(100) = 'OR' DECLARE @Replacer AS VARCHAR(100) = 'K' DECLARE @IgnorePattern AS VARCHAR(100) = '%[a-z]%' DECLARE @OutputString AS VARCHAR(100) = '' IF OBJECT_ID('tempdb..#z') IS NOT NULL DROP TABLE #z SELECT CHARINDEX(@SubString,@InputString,Number) Inxx , rnk INTO #z FROM ( SELECT CHARINDEX(@SubString,@InputString,N.Number) inx, ROW_NUMBER() OVER (ORDER BY CHARINDEX(@SubString,@InputString,N.Number) DESC) rnk , Number FROM dbo.Numbers N WHERE LEN(@InputString) >= N.Number AND SUBSTRING(@InputString, N.Number, LEN(@SubString)) = @SubString AND PATINDEX(@IgnorePattern, SUBSTRING(@InputString, N.Number + LEN(@SubString), 1)) = 0 AND 0 = CASE WHEN @IgnorePattern = '' THEN 0 ELSE PATINDEX(@IgnorePattern, SUBSTRING(@InputString, N.Number - 1, 1)) END )t ;WITH CTE AS ( SELECT STUFF(@InputString,Inxx,LEN(@SubString),@Replacer) finals , rnk FROM #z WHERE rnk = 1 UNION ALL SELECT STUFF(finals,z.Inxx,LEN(@SubString),@Replacer) , c.rnk + 1 rnk FROM #z z INNER JOIN CTE c ON c.rnk + 1 <= z.rnk ) ,CTE1 AS ( SELECT finals , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM CTE ) SELECT TOP 1 @OutputString = finals FROM CTE1 ORDER BY rnk DESC SELECT @OutputString Output IF OBJECT_ID('tempdb..#z') IS NOT NULL DROP TABLE #z -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com