Repository: tobymao/sqlglot

Issue:
double pipe operator is not string concatenation in ms sql server
Double pipe (`||`) [only applies to Azure SQL database](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-transact-sql?view=sql-server-ver16), while `+` [applies to Azure SQL database as well as a bunch of others](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-transact-sql?view=sql-server-ver16).

Discussion:
@cpcloud: I will take a crack at fixing this.

Solution patch:
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index d143126138..65002427c2 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -3,7 +3,7 @@
 import datetime
 import re
 import typing as t
-from functools import partial
+from functools import partial, reduce
 
 from sqlglot import exp, generator, parser, tokens, transforms
 from sqlglot.dialects.dialect import (
@@ -1192,3 +1192,8 @@ def declareitem_sql(self, expression: exp.DeclareItem) -> str:
         def options_modifier(self, expression: exp.Expression) -> str:
             options = self.expressions(expression, key="options")
             return f" OPTION{self.wrap(options)}" if options else ""
+
+        def dpipe_sql(self, expression: exp.DPipe) -> str:
+            return self.sql(
+                reduce(lambda x, y: exp.Add(this=x, expression=y), expression.flatten())
+            )


Test patch:
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 19eb9723ff..7114750d2c 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -420,6 +420,11 @@ def test_tsql(self):
             "SELECT val FROM (VALUES ((TRUE), (FALSE), (NULL))) AS t(val)",
             write_sql="SELECT val FROM (VALUES ((1), (0), (NULL))) AS t(val)",
         )
+        self.validate_identity("'a' + 'b'")
+        self.validate_identity(
+            "'a' || 'b'",
+            "'a' + 'b'",
+        )
 
     def test_option(self):
         possible_options = [