Add Remove_Leading_and_Trailing_Backward_and_Forward_Slashes script

ktaranov · ktaranov · commit 895e22134f77 · 2019-10-16T14:53:15.000+03:00
diff --git a/Scripts/Remove_Leading_and_Trailing_Backward_and_Forward_Slashes.sql b/Scripts/Remove_Leading_and_Trailing_Backward_and_Forward_Slashes.sql
@@ -0,0 +1,15 @@
+/*
+https://sqlperformance.com/2019/10/t-sql-queries/overlooked-t-sql-gems
+*/
+
+SELECT string, leading_slash_count, trailing_slash_count,
+    STUFF(STUFF(string, LEN(string) - trailing_slash_count + 1,
+trailing_slash_count, ''), 1, leading_slash_count, '') AS new_string
+FROM ( VALUES
+        (CAST('//\\ remove leading and trailing backward (\) and forward (/) slashes \\//' AS varchar(200))),
+        ('//\\**remove leading and trailing backward (\) and forward (/) slashes**\\//')
+) AS test_data(string)
+CROSS APPLY (
+SELECT PATINDEX('%[^/\]%', string) - 1 AS leading_slash_count,
+        PATINDEX('%[^/\]%', REVERSE(string)) - 1 AS trailing_slash_count
+) AS ca1;
