Let's say I have a table:
Current table:
title_id title_name title_qty
1 A.I. Artificial Intelligence 2
2 Batman Begins 40
3 2012 7
4 101 Dalmatians 23
5 Act of Valor 1
6 Batman 50
7 20 Million Miles to Earth 340
I would like to have an output of:
Desired output:
title_id title_name title_qty title_char
4 101 Dalmatians 23 #
7 20 Million Miles to Earth 340 #
3 2012 7 #
1 A.I. Artificial Intelligence 2 A
5 Act of Valor 1 A
6 Batman 50 B
2 Batman Begins 40 B
Based on what I read here in Stack Overflow, the fastest way to check if a character is a number is to use LIKE '[0-9]%' so I came up with
Query:
SELECT *, CASE WHEN LEFT(title_name,1) LIKE '[0-9]%' THEN "#" ELSE LEFT(title_name,1) END as title_char FROM title ORDER BY title_name
Output:
title_id title_name title_qty title_char
4 101 Dalmatians 23 1
7 20 Million Miles to Earth 340 2
3 2012 7 2
1 A.I. Artificial Intelligence 2 A
5 Act of Valor 1 A
6 Batman 50 B
2 Batman Begins 40 B
As shown above, it doesn't work at all. But if I change the LIKE to match a single digit, it works:
SELECT *, CASE WHEN LEFT(title_name,1) LIKE '1%' THEN "#" ELSE LEFT(title_name,1) END as title_char FROM title ORDER BY title_name
title_id title_name title_qty title_char
4 101 Dalmatians 23 #
7 20 Million Miles to Earth 340 2
3 2012 7 2
1 A.I. Artificial Intelligence 2 A
5 Act of Valor 1 A
6 Batman 50 B
2 Batman Begins 40 B
I initially thought that it doesn't work because my query is a bit complicated so I did a simple SELECT WHERE. The LIKE is added after the WHERE.
No output (when there should be three):
SELECT * FROM title WHERE title_name LIKE '[0-9]%' ORDER BY title_name
Working as expected:
SELECT * FROM title WHERE title_name LIKE '1%' ORDER BY title_name
SELECT * FROM title WHERE title_name LIKE '2%' ORDER BY title_name
According to the SQL documentation,
A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.
Anyone knows why it doesn't work if you used character class [0-9]? As a beginner, please do correct me if I am wrong. But I am curious why it works if I use LIKE '1%' or any digit while LIKE '[0-9]%' won't give any result? Documentation says it should match any digit right? If casting it as integer is a requirement it shouldn't work on all my tests, right? Because I didn't cast it before doing the LIKE comparison.
SQL fiddle: http://sqlfiddle.com/#!2/492906/7