Query with like using a parameter to a MSSQL database

[osantos]

I am trying to filter a wuery using like and a parameter with
LIKE ‘%’ || :User || ‘%’

When doing so, Base asks for the parameter value and then I get a MSSQL error saying that the SQL command has an error near |

How should I go arround this and be able to filter using a parameter?

In case it helps, this is the query I am trying to run, and it seems quite clear to me that the error I am getting is specific to MSSQL:
SELECT TRIM ( "CobroVEstCobro"."IdFinca" ) "IdFinca", "Finca"."Direccion" "Finca", CONCAT( "NomTitular", ' ', "ApellidoTitular" ) "Llogater", CONCAT( "Escalera", ' ', "Piso", "Puerta" ) "Pis", "CobroVEstCobro"."FechaMov" "Data_pagament", "CobroVEstCobro"."FechaExped" "Data_rebut", "CobroVEstCobro"."NumRecibo" "Num_rebut", "CobroVEstCobro"."Importe" "Import", "EstCobro"."Descripcion" "Forma_pagament" FROM "VFIN1"."dbo"."Finca" "Finca", "VFIN1"."dbo"."CobroVEstCobro" "CobroVEstCobro", "VFIN1"."dbo"."EstCobro" "EstCobro", "VFIN1"."dbo"."DepartamentoV" "DepartamentoV", "VFIN1"."dbo"."Inquilino" "Inquilino" WHERE "Finca"."Id_Finca" = "CobroVEstCobro"."IdFinca" AND "EstCobro"."Id_EstCobro" = "CobroVEstCobro"."IdEstCobro" AND "DepartamentoV"."IdFinca" = "CobroVEstCobro"."IdFinca" AND "DepartamentoV"."Id_Depto" = "CobroVEstCobro"."IdDepto" AND "Inquilino"."Id_Inquilino" = "CobroVEstCobro"."IdInquilino" AND "Inquilino"."IdDepto" = "CobroVEstCobro"."IdDepto" AND "Inquilino"."IdFinca" = "CobroVEstCobro"."IdFinca" AND CONCAT( "NomTitular", ' ', "ApellidoTitular" ) LIKE '%' || :Llogater || '%'

[Wanderer]

I hope the nice ‘%’ is really '%' (and caused by this site) or this would be the first error.
.
Some databases dont’t like || and there you have to use CONCAT-function for the same purpose.
(But I don’t use MS-SQL/MS-Server).
.
If this did not help please show your complete statement. For quoting use either a single backtick ` on both sides or 3 of them for bigger code-blocks.
Compare: This is the guide - How to use the Ask site? - #6 by erAck

[RobertG]

@osantos Really MSSQL, not MySQL? With MySQL the pipes won’t work.
CONCAT('%' , :User , '%')
will be the code for MySQL. If you are using MSSQL - see for the special code for concatening.

[osantos]

Yes,m I am connecting to Microsofts MSSQL. I already tried with
LIKE CONCAT(’%’ , :User , ‘%’)
in the query editor but Base keeps treating it like a string instead of a command.
I have also tried using + signs to concatenate but then I get “error in the SQL statment” when I try to save it.

[RobertG]

Works here without any problem with MariaDB on OpenSUSE.

How do you connect to MSSQL?
Might be it has something to do with Edit → Database → Advanced settings → Special settings → "Replace named parameters with '?'"

[osantos]

@RobertG I connect to MSSQL through an ODBC connection, I am runing it on Windows 11

With regards to your comment … if I enable the option “Replace named parameters with ‘?’” and I try to run the query using LIKE ‘%’ || :User || ‘%’ as the criteria, I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '|'.
If I dissable the option, the error I get is:

Invalid descriptor index
You tried to set a parameter at position “1” but there is/are only “0” parameter(s) allowed. One reason may be that the property “ParameterNameSubstitution” is not set to TRUE in the data source.

In case it helps, this is the query I am trying to run, and it seems quite clear to me that the error I am getting is specific to MSSQL:
SELECT TRIM ( "CobroVEstCobro"."IdFinca" ) "IdFinca", "Finca"."Direccion" "Finca", CONCAT( "NomTitular", ' ', "ApellidoTitular" ) "Llogater", CONCAT( "Escalera", ' ', "Piso", "Puerta" ) "Pis", "CobroVEstCobro"."FechaMov" "Data_pagament", "CobroVEstCobro"."FechaExped" "Data_rebut", "CobroVEstCobro"."NumRecibo" "Num_rebut", "CobroVEstCobro"."Importe" "Import", "EstCobro"."Descripcion" "Forma_pagament" FROM "VFIN1"."dbo"."Finca" "Finca", "VFIN1"."dbo"."CobroVEstCobro" "CobroVEstCobro", "VFIN1"."dbo"."EstCobro" "EstCobro", "VFIN1"."dbo"."DepartamentoV" "DepartamentoV", "VFIN1"."dbo"."Inquilino" "Inquilino" WHERE "Finca"."Id_Finca" = "CobroVEstCobro"."IdFinca" AND "EstCobro"."Id_EstCobro" = "CobroVEstCobro"."IdEstCobro" AND "DepartamentoV"."IdFinca" = "CobroVEstCobro"."IdFinca" AND "DepartamentoV"."Id_Depto" = "CobroVEstCobro"."IdDepto" AND "Inquilino"."Id_Inquilino" = "CobroVEstCobro"."IdInquilino" AND "Inquilino"."IdDepto" = "CobroVEstCobro"."IdDepto" AND "Inquilino"."IdFinca" = "CobroVEstCobro"."IdFinca" AND CONCAT( "NomTitular", ' ', "ApellidoTitular" ) LIKE '%' || :Llogater || '%'

[RobertG]

@osantos

if I enable the option “Replace named parameters with ‘?’” and I try to run the query using LIKE ‘%’ || :User || ‘%’ as the criteria, I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '|'.

This seems to be option you have to activate. But MSSQL doesn’t know anything about ||. So you could try CONCAT or + instead: LIKE '%' + :User + '%' or LIKE CONCAT( '%' , :User , '%').
All won’t work without “Replace named parameters with ‘?’” enabled.

[Wanderer]

You may try first LIKE :User to test the parameter. If this works, try CONCAT. (Another -not recommended- way might be '%'+ :User +'%'. for MS)

[osantos]

I finally made it work but I could only do so directly in the SQL Editor and not the graphic one.
I had to use the LIKE CONCAT( ‘%’ , :param , '%")

Spacing between the concat parameters seems to be critical since otherwise I would get an error. Problem is if I later open this same query on the graphic editor, then it throws an error…