Topic on Talk:Quarry

Jump to navigation Jump to search

Case-insensitive search of usernames

3
AlanM1 (talkcontribs)

1. I did select user_name from user where user_name like '%chandandolui%'; and got two results: Iamchandandolui and Mr.chandandolui.

2. I then did select user_name from user where UPPER(user_name) like '%CHANDANDOLUI%'; which ran for about a minute and returned no rows. Shouldn't I get at least the same two rows? I know of at least two more (Chandandolui and Chandandolui1) that should match the case-insens search.

3. If I run select user_name from user where UPPER(user_name) like '%chandandolui%';, which shouldn't match anything I think, it returns the same two rows as the first case.

I'm a little rusty but what am I missing? —[AlanM1(talk)]— 08:35, 5 September 2020 (UTC)

Matěj Suchánek (talkcontribs)

user_name is varbinary. According to , something like UPPER(CONVERT(user_name USING utf8mb4)) could do the trick.

AlanM1 (talkcontribs)

Thanks! The varbinary type didn't occur to me, but I can guess at the reason. en:WP:VPT came up with using RLIKE '(?i)chandandolui', which seem like an easier solution for me to remember (regex comparisons were always on my SQL wishlist when I was working with it :) ). —[AlanM1(talk)]— 11:26, 5 September 2020 (UTC)

Reply to "Case-insensitive search of usernames"