假设我有一张桌子:
当前表格:
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
我希望得到一个输出:
期望的输出:
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
基于我在Stack Overflow中读到的内容,检查字符是否为数字的最快方法是使用LIKE'[0-9]%’,所以我想出了
查询:
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
输出:
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
如上所示,它根本不起作用.但是,如果我将LIKE更改为匹配单个数字,则可以:
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
我最初认为它不起作用,因为我的查询有点复杂所以我做了一个简单的SELECT WHERE.在WHERE之后添加LIKE.
没有输出(当应该有三个):
SELECT * FROM title WHERE title_name LIKE '[0-9]%' ORDER BY title_name
按预期工作:
SELECT * FROM title WHERE title_name LIKE '1%' ORDER BY title_name
SELECT * FROM title WHERE title_name LIKE '2%' ORDER BY title_name
根据SQL文档,
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.
任何人都知道如果你使用字符类[0-9]它为什么不起作用?作为初学者,如果我错了,请纠正我.但我很好奇为什么它可以使用LIKE’1%’或任何数字,而LIKE'[0-9]%’不会给出任何结果?文档说它应该匹配任何数字吧?如果将它作为整数强制转换是一项要求,它应该不适用于我的所有测试,对吧?因为在进行LIKE比较之前我没有投出它.