LIKE wildcards in Named Query

Ignition
1

Quick question, is it possible to use a LIKE wildcard in a named query? If so how do you do it?

2

I would pass the entire pattern of the LIKE as a parameter.

3

Thats what I was afraid of, I have made a called template that has a StringInput property that is used in several places in my project. For my name query I am just handing it the StringInput value. Any suggestions of how to deal with that?

4

Use your DB’s concat function to construct the complete pattern from the static part and your string parameter.

5

Ah, thank you very much.

6

Ok, so I got the concat to function like I wanted but I am having an issue with the filtering. Here is my code:

SELECT 
	HangerNumber,
	Room,
  	PartNumber,
  	PartDescription,
  	WorkOrder,
  	Customer, 
  	RecipeNumber,
  	CoatNumber,
  	RobotProgram,
  	EventDescription,
  	Temperature,
  	Humidity,
  	Weight,
  	TimeStamp,
  	Username
FROM 
	event_history
WHERE
	((TimeStamp >= :StartTime) AND (TimeStamp <= :EndTime)) AND
	((PartNumber LIKE concat(:PartNumber, '%')) OR
 (0 = :PartNumber)) AND
	((WorkOrder LIKE concat(:WorkOrder, '%')) OR (0 = :WorkOrder)) AND
	((PartDescription = concat('"':PartDescription, '%"')) OR (0 = :PartDescription)) AND
	((Customer LIKE concat(:Customer, '%')) OR (0 = :Customer))

The filters work when the data only includes numbers but wont filter if that are letters in any of the cells. Have any idea what I am doing wrong?

1 Like