· 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 ?)"

Discussion (5)1
Log in or sign up to continue