MYSQL 如何在选择查询中使用修剪

MYSQL How to use trim in select query

提问by Bharanikumar

my table have set of records around 50,

我的桌子上有大约 50 条记录，

in the table i have column called USERNAME, but some of username leading and trailing have the white space ,

在表中，我有一列名为USERNAME，但一些用户名前导和尾随有空格，

so am not getting exact order result because of white space,

因为空白，所以没有得到确切的订单结果，

So tell me how to use the trim in SELECTquery ,

所以告诉我如何在SELECT查询中使用修剪，

Thanks

谢谢

回答by OMG Ponies

You can use TRIMin the ORDER BY:

您可以在 ORDER BY 中使用TRIM：

ORDER BY TRIM(username)

...but this will only trim excess space on the left and right side of the text, not in between.

...但这只会修剪文本左侧和右侧的多余空间，而不是介于两者之间。

Using TRIM in the SELECT is as easy as:

在 SELECT 中使用 TRIM 非常简单：

SELECT TRIM(username) AS username
  FROM your_table

回答by slon

You can use REPLACEin your query to remove all whitespaces from strings, e.g.:

您可以在查询中使用REPLACE从字符串中删除所有空格，例如：

SELECT REPLACE(A.Postal, ' ', '') AS Postal FROM ADDRESS

Be aware that this will remove all whitespaces, not only the leading and tailing ones.

请注意，这将删除所有空格，而不仅仅是开头和结尾的空格。