Menu
I am adding a few new features to a small project i'm working on and one of them is alpha pagination which looks like
# 0-9 A B C D E ... X Y Z
I can easily fetch items by their first letter using something like
SELECT * FROM ... WHERE name LIKE 'A%' ...
Grouping everything that starts with a number and all other characters is a little more difficult, I assume it would have to use MySQLs REGEXP.
Just to be clear, I need help creating two queries which will fetch all rows where
917
In SQL Server, we can use NOT LIKE with a list of alphanumeric characters that we want to exclude from the result: SELECT c1 FROM t1 WHERE c1 NOT LIKE '%[a-zA-Z0-9]%'; That returns rows that only contain non-alphanumeric characters.
Using LIKE clause Val LIKE '%[A-Z]%', it ensures that string should contain alphanumeric characters. Val LIKE '%[0-9]%', it ensures that string should contain numeric characters. Lets see the output of T-SQL, you can see it returns only alphanumeric string only.
You can use these SQL data types to store alphanumeric data: CHAR and NCHAR data types store fixed-length character literals.
First character is numeric:
SELECT * FROM ... WHERE name REGEXP '^[0-9]';
First character is not alphanumeric:
SELECT * FROM ... WHERE name REGEXP '^[^0-9A-Za-z]';
(Note that this is distinct from NOT REGEXP ^[0-9A-Za-z], because you seem to only want to match when there is in fact a first character.)
You can probably substitute [^[:alnum:]] for [^0-9A-Za-z], but I haven't tested it. You can certainly substitute [[:digit:]] for [0-9], but it's longer. :-)
Also see the MySQL REGEXP Reference.
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
