Menu
my table have set of records around 50,
in the table i have column called USERNAME, but some of username leading and trailing have the white space ,
so am not getting exact order result because of white space,
So tell me how to use the trim in SELECT query ,
Thanks
612
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Indicates that prefixes from both left and right are to be removed. Indicates that only leading prefixes are to be removed. Indicates that only trailing prefixes is to be removed.
The TRIM() function returns a string that has unwanted characters removed. Note that to remove the leading spaces from a string, you use the LTRIM() function. And to remove trailing spaces from a string, you use the RTRIM() function.
MySQL a-z in Telugu You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.
You can use TRIM in the ORDER BY:
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(username) AS username
FROM your_table
You can use REPLACE in your query to remove all whitespaces from strings, e.g.:
SELECT REPLACE(A.Postal, ' ', '') AS Postal FROM ADDRESS
Be aware that this will remove all whitespaces, not only the leading and tailing ones.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
