cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

Wildcard in SQL query with input param

ague
12-Amethyst

Wildcard in SQL query with input param

I need to make a SQL query in Thingworx. The query should read (if I were creating it directly):

SELECT * FROM MyDB.MyTable where Name like '%Name_Input%'

 

Another PTC forum post had recommended this as a possible solution:

SELECT * FROM MyDB.MyTable where Name like '%' || [[Name_Input]] || '%'

 

But this query (above) does not work. The post was from the year 2014 and I'm currently using 8.4.1, so I'm guessing that's the issue.....

 

Solutions anyone?

13 REPLIES 13
posipova
20-Turquoise
(To:ague)

This could have been changed due to the changed security policies. I will verify and get back to you/this thread with an update.

posipova
20-Turquoise
(To:posipova)

This was part of security updates, works to spec.

ague
12-Amethyst
(To:posipova)

So, when you say, 'works to spec', are you saying that this SQL query should in fact work? 

posipova
20-Turquoise
(To:ague)

Wildcard should not work, sql queries should work.

ague
12-Amethyst
(To:posipova)

So, how can I execute a SQL query that needs to take in a variable? What's the work-around for this?

posipova
20-Turquoise
(To:ague)

ague
12-Amethyst
(To:posipova)

It tells me how to use a variable from a user (fine, I know how to do that), but it doesn't mention being able to user wildcards WITH the variable. 

 

I know I can use wildcards WITHOUT a variable.

posipova
20-Turquoise
(To:ague)

I don't think we support wildcard without a variable/security patches.

ague
12-Amethyst
(To:posipova)

Please elaborate on what 'variable patches' are. 

posipova
20-Turquoise
(To:ague)

I apologize for wording that poorly. "/" Should be read as "(security patches)".
ague
12-Amethyst
(To:posipova)

What security patches are required then? What updates do I need? 

posipova
20-Turquoise
(To:ague)

It's already part of the newer build that's why the suggestion from older (2014) thread did not work. I'll place an improvement request on your behalf.
JT_OU812
10-Marble
(To:ague)

I had the same issue, here's what I found to work, this works in 9.4.1:
SELECT * FROM MyDB.MyTable where field_values LIKE '%'+[[source]]+'%'

Top Tags