Does regexp work in Heidi?

I love HeidiSQL and have used it for a few years now. Just today however I found myself needing to use regexp - just to find columns that start with capital letters (or not). However, I can't for the life of me figure out how to use it in this program and I'm not unfamiliar with regexp itself.

I was using Llama-3-70b to help me code some queries and it suggested REGEXP_LIKE('^[A-Z]') or similar - that doesn't exist. Then it suggested I use RLIKE instead; that didn't outright error but doesn't seem to do anything either.

I found the REGEXP_INSTR and REGEXP_SUBSTR under the right click > insert function > string stuff, but I can't figure out those either. They also don't seem to do anything or I don't understand how they work at any rate. I tried this in both actual queries and also in filters, neither seemed to work.

Would appreciate any help.

REGEXP_LIKE is only available on newer server versions. Try this one instead:

SELECT ... WHERE yourcolumn REGEXP BINARY '^[A-Z]';

Note that you need the BINARY operator if the column on which you operate has a non-binary collation (like VARCHAR or MEDIUMTEXT). On most text columns any string function works case insensitive, because the default text collations are defined as such.

Thanks for response. I gave that a go but unfortunately it also errors. I used this: SELECT id, NAME FROM npc_types WHERE name BINARY '^[A-Z]';

Error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BINARY '^[A-Z]' at line 3

Would you say this is actually an issue with my version of MariaDB, not perhaps Heidi? I hadn't considered that before. I am on latest version of Heidi (12.7.0.6850)

Actually I spoke too soon, I found this stackoverflow post that had an example which I tried and it worked. I didn't realize how BINARY works but I guess it's an addition to REGEXP. IE this works:

SELECT id, NAME 
FROM npc_types 
WHERE name REGEXP BINARY '^[A-Z]';

That's pretty exactly what I wrote, or?

BINARY is not an addition to REGEXP. You can use the BINARY cast in any string function. It casts the used collation of a field for the query to a case sensitive collation.

Well, you may say that but the below code absolutely didn't work for me. Adding REGEXP fixed it. RLIKE also worked this way. But neither of them worked without both BINARY and either RLIKE or REGEXP. The field in question is just a text field if that matters.

WHERE name BINARY '^[A-Z]';

You know on looking again today because I was puzzled by you saying that, I realize that you're right you did pretty much say it the first time. I dunno how I got confused or missed that, in my defense I've had a really bad cold for days and probably shouldn't be editing my DB, but oh well. Appreciate the help anyhow.