Rewriting SQL-statement
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2018-02-28 16:05

I am making an online database.
I use myPhpAdmin 4.7.8


When i run this sql-statement i get no names for my referees but
instead i get 0's
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
Scheids_ID, REPLACE(RTRIM(COALESCE(Voornaam || " ", "") ||
COALESCE(Tussenvoegsel || " ", "") || COALESCE(Achternaam, "")), " ",
" ") AS Scheidsrechter FROM tbl_ScheidsrechtersHow to rewrite it so
that myPhpAdmin gives me the correct results

----------------------------------------------------------------------
rvk · 2018-02-28 19:13

First of all (the reason for the 0s) is: What does || do in MySQL ??

|| is used in Oracle and Firebird/Interbase to concatenate strings but
for MySQL this doesn't work. There you need to use the CONCAT()
function.

Second... Did you have a replace for " " to " "? (single space to
single space)
I think you meant double space to single space.

The complete SQL would be something like this:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
Scheids_ID,  TRIM(REPLACE(CONCAT(    COALESCE(Voornaam,''), ' ',
COALESCE(Tussenvoegsel,''), ' ', COALESCE(Achternaam,'')  ), '  ', '
')) AS ScheidsrechterFROM tbl_Scheidsrechters

----------------------------------------------------------------------
madref · 2018-02-28 20:09

I tried that first. Replacing the dubble quotes for single quotes.
And it didn't work


I uses it for the first time i could find it here : [size=78%]http://f
orum.lazarus.freepascal.org/index.php/topic,27755.0.html[/size]


But i think i got it from a different forum

----------------------------------------------------------------------
rvk · 2018-02-28 20:15

Was that with MySQL database? || isn't MySQL.
(and I see in that other topic you used to work with SQLite3 which is
different from MySQL)

You can use double quotes if you like.
The usage of double quotes depends on the MySQL-mode while single
quotes should always work.

I checked here and the SQL I gave you should work (it did for me).