-1

Im using Sqlite3 and mysql database for one application.

I wrote a query to concat two columns of a table as

select CONCAT(firstname,' ',lastname)as fullname from customers;

in sqlite i can write it as

select firstname||' '||lastname as fullname from customers;

but i want it to work in both the database like Sqlite and Mysql with one query.

vijay kumar
  • 287
  • 2
  • 8
  • 28
  • You can just concat them in PHP. – nice_dev Nov 29 '21 at 04:40
  • @mickmackusa This question isn't really about database drivers, it is about finding a way to get MySQL and SQLite to behave in the same way. – Tim Biegeleisen Nov 29 '21 at 04:44
  • @vijaykumar Doing it with PHP will be more generic as you wouldn't have to care about database specific settings if in case tomorrow you decide to go for PostgreSQL or Oracle etc. – nice_dev Nov 29 '21 at 04:49
  • @nice that's true for the presented case with SELECT, but if a query ever needed to involve `CONCAT()` in a WHERE or GROUP BY or HAVING clause, then php will not suffice or would require php to do filtering that sql should be doing. – mickmackusa Nov 29 '21 at 04:50
  • @mickmackusa Ok, but in that case, integrating multiple DB types for a single application would be a bad idea, because this way writing generic SQL would require us to take care about a lot of DB specific settings. – nice_dev Nov 29 '21 at 04:53
  • 1
    I don't disagree with you there @nice. – mickmackusa Nov 29 '21 at 04:56

1 Answers1

1

MySQL actually has a server mode ANSI, which itself includes a mode PIPES_AS_CONCAT. In ANSI mode, MySQL will accept double pipes for string concatenation, as SQLite does by default. To change your MySQL server mode to ANSI, use:

SET GLOBAL sql_mode = 'ANSI';   -- for the entire server
SET SESSION sql_mode = 'ANSI';  -- for your particular session
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Yes, and also it's worth mentioning that if you do this, you must use the `OR` keyword for the OR boolean operation. If `||` is configured to do string concatenation, it will no longer do OR. This might change the effect of any existing code you have. – Bill Karwin Nov 29 '21 at 07:34