Focal Point
[SOLVED] Using a variable in a like statement

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4347063196

June 12, 2019, 03:11 PM
ccollier
[SOLVED] Using a variable in a like statement
I want to use a like statement in my report, but what the like is equal to depends on the user. What I currently have is

 TABLE FILE sp_failure_details
PRINT filename
      description
WHERE filename LIKE '&TEST'
END 


But it has to be an exact filename, not a like. I want something that looks like '123%' with '123' being the variable passed through by the user.

This message has been edited. Last edited by: ccollier,


WebFOCUS 8.2.01M on Windows 10
June 12, 2019, 03:15 PM
BabakNYC
Append a % to the beginning and end of the variable so you can search for anything you want.

  
-SET &TEST='%'||&TEST||'%';
TABLE FILE CAR
PRINT CAR BY COUNTRY
WHERE CAR LIKE '&TEST';
END



WebFOCUS 8206, Unix, Windows
June 12, 2019, 03:24 PM
ccollier
Thank you Babak! I knew there had to be a simple solution like that, but I just couldn't think of it. Guess I still have a lot to learn when it comes to WebFOCUS


WebFOCUS 8.2.01M on Windows 10
June 12, 2019, 04:08 PM
MartinY
Maybe more simple
TABLE FILE CAR
PRINT CAR BY COUNTRY
WHERE CAR CONTAINS '&TEST';
END



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
June 12, 2019, 05:00 PM
BabakNYC
In case you want to go down the rabbit hole here's a comprehensive explanation of LIKE and CONTAINS. Interesting nuance.

https://webfocusinfocenter.inf...r/source/03PRM21.htm


WebFOCUS 8206, Unix, Windows
June 13, 2019, 07:24 AM
MartinY
quote:
Originally posted by BabakNYC:
In case you want to go down the rabbit hole here's a comprehensive explanation of LIKE and CONTAINS. Interesting nuance.

https://webfocusinfocenter.inf...r/source/03PRM21.htm

Interesting the use of _ in a LIKE.
Similar as using WHERE EDIT(JOBCODE, '$9') EQ '1';

I'm wondering which syntax is the more performant...?


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007