mysql SELECT 与 SUBSTRING、LOWER 和 REPLACE 命令
我尝试对公司表的公司名称 (cpnm) 字段进行选择，但只给出前 6 个字符，替换所有空格并使其小写。
SELECT *,
SUBSTRING(LOWER(cpnm), -LENGTH(cpnm), 6) as test
FROM company
LIMIT 100
上面的工作正常，但一旦我尝试添加替换空格（如下所示），它就不会返回结果。
SELECT *,
SUBSTRING(LOWER(REPLACE(cpnm, ' ', '')), -LENGTH(cpnm), 6) as test
FROM company
LIMIT 100
有什么想法吗？
Im trying to do a select on the company name(cpnm) field of our company table but only giving me the first 6 characters, replacing all spaces plus making it lowercase.
SELECT *,
SUBSTRING(LOWER(cpnm), -LENGTH(cpnm), 6) as test
FROM company
LIMIT 100
The above works fine but as soon as I try to add the replace spaces (shown below), it doesnt give back results.
SELECT *,
SUBSTRING(LOWER(REPLACE(cpnm, ' ', '')), -LENGTH(cpnm), 6) as test
FROM company
LIMIT 100
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我猜想通过替换空格会改变字符串的长度，因此
substr()不会像您期望的那样工作。尝试
选择*，！SUBSTRING(LOWER(REPLACE(cpnm, ' ', '')), -LENGTH(REPLACE(cpnm, ' ', '')), 6) 作为测试
I guess by replacing the spaces you change the length of the string, therefore
substr()doesn't work like you expect it.Try
SELECT *,instead!SUBSTRING(LOWER(REPLACE(cpnm, ' ', '')), -LENGTH(REPLACE(cpnm, ' ', '')), 6) as test