Search with placeholder (_) doesn't work

Scriptcase 9 Bugs
#1

In use: Version 9.13.014(S).
After updates the search with the placeholder _ () in my database-project doesn’t work. With % it works.
A search like '198’ returns no results.
With ‘198%’ I receive 1988, 1989, 19889 and so on.
Before the updates everything was fine.

#2

@kilgus,

Could you please share with us the example you are trying to use? Are you using any of the search macros?

Best regards!

#3

I reported this long time ago. They replied that “_” is part of SQL code that is why it is causing the issues and should not be used in search fields. I do not know if I should cry or laugh.

#4

It’s really a basic functionality of LIKE in MySQL, MariaDB and MSSQL, I don’t think that Scriptcase as anything to do with that

SELECT something FROM table WHERE field LIKE ‘198_’

It has to be use with the LIKE function

Be careful when using that it often a security risk for SQL injection

#5

With Scriptcase 8 and Scriptcase 9, Version 9.9.007 (4) all thinks work fine (MySQL).

Only after update to the Version 9.13.014 (5) searching with wildcard _ does’nt work (neither locally nor on the internet). The placeholder % still works.

#6

Did you try in Debug mode to check what Scriptcase is really sending to MySQL

If it’s modified then you can blame on Scriptcase about it

#7

That is what AI think about it

That is a very specific headache. If % (multi-character) works but _ (single-character) suddenly fails, you are likely hitting a multi-byte character issue or a collation bug.

Here are the three most common reasons this happens:

  1. Multi-byte Characters (UTF8mb4)

The _ wildcard matches one character, not one byte.

  • The Issue: If your data contains special characters (like emojis, smart quotes, or accented letters) and your Connection Character Set doesn’t match the table’s character set, MySQL might miscalculate the character length.
  • The Test: Try running SET NAMES 'utf8mb4'; before your query to ensure the connection is handling multi-byte characters correctly.
  1. The “Binary” Collation Trap

Check if your column or query is using a _bin (binary) collation.

  • The Issue: Binary collations compare byte-by-byte. In some MySQL 8.0 versions, there were known optimizer bugs where the _ wildcard failed to correctly match characters in indexed columns using utf8mb4_0900_bin.
  • The Fix: Try forcing a case-insensitive collation in the query to see if it starts working:

sql

SELECT * FROM your_table 
WHERE your_column COLLATE utf8mb4_unicode_ci LIKE 'your_pattern_';
  1. Hidden “Invisible” Characters

Since % works (it just looks for anything after), it might be hiding characters you don’t realize are there.

  • The Issue: Your data might have a trailing newline (\n), carriage return (\r), or null byte.
  • The Math: If your data is A_ (two characters), LIKE 'A_' works. But if there is a hidden newline (A_\n), LIKE 'A_' will fail because the string is now 3 characters long.
  • The Test: Run SELECT HEX(your_column) FROM your_table for one of the failing rows. If the hex string is longer than expected (e.g., more than 2 hex digits per character), you have hidden data.
  1. Optimizer Index Bug

Sometimes the index itself is “corrupt” or the optimizer is misreading the fixed-length nature of _.

  • The Test: Run the query using IGNORE INDEX:

sql

SELECT * FROM your_table IGNORE INDEX (your_index_name) 
WHERE your_column LIKE 'abc_';

If it works with IGNORE INDEX, you need to REPAIR or OPTIMIZE that table.

#8

Thanks for the help.

It’s a mystery.
My (temporary) solution: I deactivated the new version (9.13.014) and reverted to version 9.9.007. That works for now.