2

I'm new to SQL(Self Taught) and to StackOverflow. So I apologize if there's a bit a newbspeak.

Currently I'm attempting to write a query within Metabase that allows a user to search multiple fields within a certain date period without being 100% exact to what they're searching for, sort of like a keyword search.

Search requests:
Date Time...(Required)
User...(optional)
String A...(optional)
String B...(optional)
String C...(optional)

SELECT 
a.DateTime,
a.User,
b.StringA,
b.StringB,
b.StringC

FROM TableA a

JOIN tableB b
   ON TableAid = TableBid

WHERE a.datetime between {{start}} and {{end}}
[[AND a.User = {{user}}]]
[[AND b.StringA = {{StringA}}]]
[[AND b.StringB = {{StringB}}]]
[[AND b.StringC = {{StringC}}]];

The syntax above appears to work correctly but only if they have the complete words inputted into their respective fields. I'm looking to use the Like operator to retrieve all rows in columns A B and C that have a string similar to the one given.

I tried using

[[AND b.StringA = Like ('%'+{{StringA}}+'%')]]

and

[[AND b.StringA = Like CONCAT('%'+{{StringA}}+'%')]]

to no avail and attempted a few of the @StringA solutions in some of the threads here but didn't have any luck.

Is anyone able to give me a hand here? Any help would be greatly appreciated.

zDogwatch
  • 57
  • 7

2 Answers2

1

So I actually wound up figuring this out after a little trial and error and wanted to come back and update the thread in case someone else had the same issue.

Rather than

[[AND b.StringA = Like CONCAT('%'+{{StringA}}+'%')]]

it needs to look like

[[and b.StringA like (concat('%',{{StringA}},'%'))]]

A minor syntactical error but man this had me stumped for a bit.

Anyways I appreciate all the help people attempted to provide and hope this helps someone in the future.

zDogwatch
  • 57
  • 7
0

You only need [[AND b.StringA = Like ({{StringA}}+'%']] to find all values that start with STRING A

or use LIKE [[AND b.StringA = Like CONCAT('%'+{{StringA}}+'%')]] to find those values at any position. [your example]

also, i'd suggest using OR when checking for optional data, AND requires all to be true before returning anything

comphonia
  • 521
  • 3
  • 10
  • I can't use that as I'm attempting to have user's enter the data without changing the SQL. Hence the need for using the field filters. Using LIKE '%STRINGA%' will only allow what's entered into the SQL editor and will need to be changed each time. So I'm trying to have the user enter a string of characters that they're attempting to search under StringA, and then have that string be assigned to a LIKE operator and populate all row's with data similar to that string. – zDogwatch Sep 28 '18 at 02:47
  • regarding the use of OR: Metabase allows the use of [[AND {{Variable}}]] as optional. Basically if they're left empty, they will be overlooked. – zDogwatch Sep 28 '18 at 02:52
  • try this: [[AND b.StringA = Like CONCAT('%'+{{StringA}}+'%')]] – comphonia Sep 28 '18 at 03:20
  • I did try that, I just mistyped the end of my code in my initial post. Thanks for the suggestion though. – zDogwatch Sep 28 '18 at 14:33