SQL regex works the wrong way

Question

I'm trying to build a SQL statement to retrieve user names in the following order

at first, return the names that start with Arabic letter, then the names that start with English letters, then the names that start with special characters.
then sort each of the three groups in ascending order.

This is my code:

SELECT `name` FROM `user` 
order by case when substring(name,1,1) like 'N[أ-ي]' then 1
              when substring(name,1,1) like '[a-zA-Z]' then 2
              else 3
              end
        ,name

The problem is that the case part always returns 3, and so the statement sorts the names in the default order(special chars first, then English letters then Arabic letters). What is the problem in my query?

@Cyval Thanks for your correction.But even when I changed the parameter the function still doesn't work. — Abozanona
– Abozanona, Commented Jan 3, 2016 at 23:17
nop, this article's example says the substring start index starts with 1... msdn.microsoft.com/hu-hu/library/ms187748.aspx and this too: dev.mysql.com/doc/refman/5.7/en/… — golddragon007
– golddragon007, Commented Jan 3, 2016 at 23:34

Community · Accepted Answer · 2017-05-23 12:31:21Z

3

You need to use regex, not like... (because you use regular expression)

SELECT `name` FROM `user` 
order by case when substring(name,1,1) regexp 'N[أ-ي]' then 1
              when substring(name,1,1) regexp '[a-zA-Z]' then 2
              else 3
              end
        ,name

Reference: MySQL CASE statement and REGEXP

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Jan 3, 2016 at 23:25

golddragon007

1,34215 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL regex works the wrong way

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related