same SQL concatenation opeartor for mysql,mssql,oracle

I am trying to use same sql statement for the above three DBMS .. but the problem is that it has string concatenation 开发者_高级运维involved but there are different ways in each dbms for concatenation operation .. but i want the single operator .. Need someone's help

---

You can perhaps get around this in your application code by using a placeholder for concatenation in your sql statements, and then replacing it with the correct style for the rdbms you are using:

select {conpre} myfield1 {conmid} myfield2 {conmid} myfield3 {conend}
  from mytable

Then in pseudo-code:

if rdbms is sqlserver
    conpre = ""
    conmid = " + "
    conend = ""
else if rdbms is mysql
    conpre = "concat("
    conmid = ", "
    conend = ")"
else if rdbms is oracle
    conpre = ""
    conmid = " || "
    conend = ""
else if
    ' etc...
end if

stmt = replace(stmt, "{conpre}", conpre)
stmt = replace(stmt, "{conmid}", conmid)
stmt = replace(stmt, "{conend}", conend)

---

I'd avoid writing your own solution to the problem and use one of the muti-database tools already available. If you have come across this problem once you will come across it again soon.

I've no affiliation with the following but you could try Datanamic Multirun

---

The simple answer is to the question seems to be no.

However...

What if you create the package dbo in Oracle? Is it not also possible in mysql to create a function called concat in a separate database called dbo, so that a function is called using the syntax dbo.concat(a, b, c)?

Unfortunately, mysql doesn't support default parameters(unless recently changed) or function overloading, so you would have to create on function for each number of arguments:

concat2(s1, s2)

concat3(s1, s2, s3)

and so on.

---

There is a way of doing this using ODBC escape sequences

SELECT {fn concat (col1, {fn concat (col2, col3)})}
FROM YourTable

From my current understanding this will work fine in SQL Server and MySQL but for Oracle is dependant upon connection method.

---

MySQL:

   SELECT CONCAT('New ', 'York ', 'City');

Output is : New York City

Oracle:

SELECT 'The city' || ' is ' || 'Paris' FROM dual;

Output is : The city is Paris

SQL Server:

SELECT 'The city' + ' is ' + 'Paris';

Output is : The city is Paris

 SELECT CONCAT('The city', ' is ', 'Paris');

Output is : The city is Paris