Amit Prajapati · Sep 12, 2019

SQL concatenation for more than 2 columns


I am implementing free search text box, which can search data in all columns of tables.

I have created DisplayName column by concatinating two column data with space.

SELECT TOP ALL FirstName||' '||LastName AS DisplayName, UserType, IsActive, RoleName, DOB FROM LISDB.ExternalUsers

Now, in where condition I want to do like search in DisplayName column but it not working.

I have tried both fn CONCAT and  || in like query but both are not working for me. 

If ((RefineSearchText'="") && ($Length($ZStrip(RefineSearchText,"<>W",""))>0)) {

        Set args($Increment(args)) = RefineSearchText   //DisplayName

        Set args($Increment(args)) = RefineSearchText   //UserType

        Set args($Increment(args)) = RefineSearchText   //RoleName

        Set query = query _ "AND ({fn CONCAT(FirstName,LastName)} like ? OR UserType like ? OR RoleName like ?)"

        #; Set query = query _ "AND (FirstName like ? OR UserType like ? OR RoleName like ?)"

0 1,212
Discussion (5)2
Log in or sign up to continue

SELECT TOP ALL FirstName||' '||LastName AS DisplayName, UserType, IsActive, RoleName, DOB FROM LISDB.ExternalUsers
Where FirstName||' '||LastName Like '%Praja%'

I have already tried that but it is not working.

Case sensitive.

Where Ucase(FirstName||' '||LastName) Like '%PRAJA%'

Just wanted to point out that concatenating columns in the where clause will mean the DB will be forced to look at every single row, which will mean slow performance if you have a large number of rows. It won't be able to use the indices on FirstName or LastName to improve performance.

Can you just check against FirstName and LastName directly?

Hi Praj, 

In the past, the following has worked for me using Intersystems Cache ODBC interface: 

{fn CONCAT(string-expression1,string-expression2)}

Hopefully this helps.

Chris Kennedy