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:

Patch:
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index d1431261..65002427 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -3,7 +3,7 @@ from __future__ import annotations
 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 @@ class TSQL(Dialect):
         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 19eb9723..7114750d 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -420,6 +420,11 @@ class TestTSQL(Validator):
             "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 = [