我可以使用MySQL TRIM()
方法通过UPDATE
清理包含前导或尾随空格的字段,如下所示:
UPDATE Foo SET field = TRIM(field);
我希望在运行之前实际看到这将影响的字段。我试过了,但返回0个结果:
SELECT * FROM Foo WHERE field != TRIM(field);
这看起来应该行得通,但事实并非如此。
有人有解决方案吗?另外,好奇为什么这不起作用……
发布于 2013-05-24 05:59:21
SELECT *
FROM
`foo`
WHERE
(name LIKE ' %')
OR
(name LIKE '% ')
发布于 2019-02-21 06:54:33
下面是一个使用RegEx的示例
SELECT *
FROM
`foo`
WHERE
(name REGEXP '(^[[:space:]]|[[:space:]]$)')
发布于 2020-08-14 03:38:43
另一种解决方案是使用SUBSTRING()
和IN
将字符串的最后和第一个字符与空格字符列表进行比较……
(SUBSTRING(@s, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s, -1, 1) IN (' ', '\t', '\n', '\r'))
...where @s
是任何输入字符串。根据需要,在比较列表中添加额外的空格字符。
下面是一个简单的测试,演示该表达式在各种字符串输入下的行为:
SET @s_normal = 'x';
SET @s_ws_leading = '\tx';
SET @s_ws_trailing = 'x ';
SET @s_ws_both = '\rx ';
SELECT
NOT(SUBSTRING(@s_normal, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_normal, -1, 1) IN (' ', '\t', '\n', '\r')) test_normal #=> 1 (PASS)
, (SUBSTRING(@s_ws_leading, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_ws_leading, -1, 1) IN (' ', '\t', '\n', '\r')) test_ws_leading #=> 1 (PASS)
, (SUBSTRING(@s_ws_trailing, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_ws_trailing,-1, 1) IN (' ', '\t', '\n', '\r')) test_ws_trailing #=> 1 (PASS)
, (SUBSTRING(@s_ws_both, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(@s_ws_both, -1, 1) IN (' ', '\t', '\n', '\r')) test_ws_both #=> 1 (PASS)
;
如果这是你要做的很多事情,你也可以为它创建一个函数:
DROP FUNCTION IF EXISTS has_leading_or_trailing_whitespace;
CREATE FUNCTION has_leading_or_trailing_whitespace(s VARCHAR(2000))
RETURNS BOOLEAN
DETERMINISTIC
RETURN (SUBSTRING(s, 1, 1) IN (' ', '\t', '\n', '\r') OR SUBSTRING(s, -1, 1) IN (' ', '\t', '\n', '\r'))
;
# test
SELECT
NOT(has_leading_or_trailing_whitespace(@s_normal )) #=> 1 (PASS)
, has_leading_or_trailing_whitespace(@s_ws_leading ) #=> 1 (PASS)
, has_leading_or_trailing_whitespace(@s_ws_trailing) #=> 1 (PASS)
, has_leading_or_trailing_whitespace(@s_ws_both ) #=> 1 (PASS)
;
https://stackoverflow.com/questions/16724574
复制相似问题