trying to limit returned users from the users table in macos

Question

trying to limit returned users from the users table in macos getting unexpected results `SELECT from users WHERE username NOT LIKE ` returns nothing but `SELECT FROM users WHERE username NOT LIKE a ` returns expected results I get d and k and n usernames but no a results v4 2 0 am I crazy

zwass · Answer

Probably to do with ` ` being a special character in sqlite <https sqlite org lang expr html> gt An underscore in the LIKE pattern matches any single character in the string

zwass · Answer

So ` ` will match any string with at least 1 character

Magneto · Answer

damn any way to escape that

zwass · Answer

```osquery gt select zwass LIKE zwass LIKE zwass LIKE ESCAPE zwass LIKE ESCAPE zwass LIKE = 1 zwass LIKE = 1 zwass LIKE ESCAPE = 0 zwass LIKE ESCAPE = 1```

Magneto · Answer

got it thanks

seph · Answer

Not sure if it s better or worse but you could use instr to see where the first character is and then filter based on that

seph · Answer

`select username from users where instr username = 1 `

Magneto · Answer

`SELECT FROM users WHERE username NOT LIKE ESCAPE ` was what I ended up with gives me most everything I anticipate

seph · Answer

My gut sense is that instr has a slight performance win over like but that it s dwarfed by the underlying api calls

zwass · Answer

^ Agreed I d go with whatever you find most readable

seph · Answer

readable 100 I found instr earlier today so I m enjoying it slightly smiling face