How to remove leading and trailing whitespace in a MySQL field?
Solution 1
You're looking for TRIM.
UPDATE FOO set FIELD2 = TRIM(FIELD2);
Seems like it might be worth it to mention that TRIM can support multiple types of whitespace, but only one at a time and it will use a space by default. You can, however, nest TRIM
s.
TRIM(BOTH ' ' FROM TRIM(BOTH '\n' FROM column))
If you really want to get rid of all the whitespace in one call, you're better off using REGEXP_REPLACE
along with the [[:space:]]
notation. Here is an example:
SELECT
-- using concat to show that the whitespace is actually removed.
CONCAT(
'+',
REGEXP_REPLACE(
' ha ppy ',
-- This regexp matches 1 or more spaces at the beginning with ^[[:space:]]+
-- And 1 or more spaces at the end with [[:space:]]+$
-- By grouping them with `()` and splitting them with the `|`
-- we match all of the expected values.
'(^[[:space:]]+|[[:space:]]+$)',
-- Replace the above with nothing
''
),
'+')
as my_example;
-- outputs +ha ppy+
Solution 2
A general answer that I composed from your answers and from other links and it worked for me and I wrote it in a comment is:
UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));
etc.
Because trim() doesn't remove all the white spaces so it's better to replace all the white spaces u want and than trim it.
Hope I could help you with sharing my answer :)
Solution 3
Just to be clear, TRIM by default only remove spaces (not all whitespaces). Here is the doc: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
Solution 4
Please understand the use case before using this solution:
trim does not work while doing select query
This works
select replace(name , ' ','') from test;
While this doesn't
select trim(name) from test;
Solution 5
It seems none of the current answers will actually remove 100% of whitespace from the start and end of a string.
As mentioned in other posts, the default TRIM
only removes spaces - not tabs, formfeeds etc. A combination of TRIM
s specifying other whitespace characters may provide a limited improvement e.g. TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM TRIM(BOTH '\f' FROM TRIM(BOTH '\t' FROM TRIM(txt)))))
. But the problem with this approach is only a single character can be specified for a particular TRIM
and those characters are only removed from the start and end. So if the string being trimmed is something like \t \t \t \t
(i.e. alternate spaces and tab characters), more TRIM
s would be needed - and in the general case this could go on indefinitely.
For a lightweight solution, it should be possible to write a simple User Defined Function (UDF) to do the job by looping through the characters at the start and end of a string. But I'm not going to do that... as I've already written a rather more heavyweight regular expression replacer which can also do the job - and may come in useful for other reasons, as described in this blog post.
Demo
Rextester online demo. In particular, the last row shows the other methods failing but the regular expression method succeeding.
Function:
-- ------------------------------------------------------------------------------------
-- USAGE
-- ------------------------------------------------------------------------------------
-- SELECT reg_replace(<subject>,
-- <pattern>,
-- <replacement>,
-- <greedy>,
-- <minMatchLen>,
-- <maxMatchLen>);
-- where:
-- <subject> is the string to look in for doing the replacements
-- <pattern> is the regular expression to match against
-- <replacement> is the replacement string
-- <greedy> is TRUE for greedy matching or FALSE for non-greedy matching
-- <minMatchLen> specifies the minimum match length
-- <maxMatchLen> specifies the maximum match length
-- (minMatchLen and maxMatchLen are used to improve efficiency but are
-- optional and can be set to 0 or NULL if not known/required)
-- Example:
-- SELECT reg_replace(txt, '^[Tt][^ ]* ', 'a', TRUE, 2, 0) FROM tbl;
DROP FUNCTION IF EXISTS reg_replace;
CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT, maxMatchLen INT)
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
DECLARE result, subStr, usePattern VARCHAR(21845);
DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
IF subject REGEXP pattern THEN
SET result = '';
-- Sanitize input parameter values
SET minMatchLen = IF(minMatchLen < 1, 1, minMatchLen);
SET maxMatchLen = IF(maxMatchLen < 1 OR maxMatchLen > CHAR_LENGTH(subject),
CHAR_LENGTH(subject), maxMatchLen);
-- Set the pattern to use to match an entire string rather than part of a string
SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
-- Set start position to 1 if pattern starts with ^ or doesn't end with $.
IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
SET startPos = 1, startInc = 1;
-- Otherwise (i.e. pattern ends with $ but doesn't start with ^): Set start position
-- to the min or max match length from the end (depending on "greedy" flag).
ELSEIF greedy THEN
SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
ELSE
SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
END IF;
WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
-- Set start length to maximum if matching greedily or pattern ends with $.
-- Otherwise set starting length to the minimum match length.
IF greedy OR RIGHT(pattern, 1) = '$' THEN
SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
ELSE
SET len = minMatchLen, lenInc = 1;
END IF;
SET prevStartPos = startPos;
lenLoop: WHILE len >= 1 AND len <= maxMatchLen
AND startPos + len - 1 <= CHAR_LENGTH(subject)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
SET subStr = SUBSTRING(subject, startPos, len);
IF subStr REGEXP usePattern THEN
SET result = IF(startInc = 1,
CONCAT(result, replacement), CONCAT(replacement, result));
SET startPos = startPos + startInc * len;
LEAVE lenLoop;
END IF;
SET len = len + lenInc;
END WHILE;
IF (startPos = prevStartPos) THEN
SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
CONCAT(SUBSTRING(subject, startPos, 1), result));
SET startPos = startPos + startInc;
END IF;
END WHILE;
IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
ELSEIF startInc = -1 AND startPos >= 1 THEN
SET result = CONCAT(LEFT(subject, startPos), result);
END IF;
ELSE
SET result = subject;
END IF;
RETURN result;
END;
DROP FUNCTION IF EXISTS format_result;
CREATE FUNCTION format_result(result VARCHAR(21845))
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
RETURN CONCAT(CONCAT('|', REPLACE(REPLACE(REPLACE(REPLACE(result, '\t', '\\t'), CHAR(12), '\\f'), '\r', '\\r'), '\n', '\\n')), '|');
END;
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl
AS
SELECT 'Afghanistan' AS txt
UNION ALL
SELECT ' AF' AS txt
UNION ALL
SELECT ' Cayman Islands ' AS txt
UNION ALL
SELECT CONCAT(CONCAT(CONCAT('\t \t ', CHAR(12)), ' \r\n\t British Virgin Islands \t \t ', CHAR(12)), ' \r\n') AS txt;
SELECT format_result(txt) AS txt,
format_result(TRIM(txt)) AS trim,
format_result(TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM TRIM(BOTH '\f' FROM TRIM(BOTH '\t' FROM TRIM(txt))))))
AS `trim spaces, tabs, formfeeds and line endings`,
format_result(reg_replace(reg_replace(txt, '^[[:space:]]+', '', TRUE, 1, 0), '[[:space:]]+$', '', TRUE, 1, 0))
AS `reg_replace`
FROM tbl;
Usage:
SELECT reg_replace(
reg_replace(txt,
'^[[:space:]]+',
'',
TRUE,
1,
0),
'[[:space:]]+$',
'',
TRUE,
1,
0) AS `trimmed txt`
FROM tbl;
Related videos on Youtube
KB.
Updated on July 08, 2022Comments
-
KB. almost 2 years
I have a table with two fields (countries and ISO codes):
Table1 field1 - e.g. 'Afghanistan' (without quotes) field2 - e.g. 'AF'(without quotes)
In some rows the second field has whitespace at the start and/or end, which is affecting queries.
Table1 field1 - e.g. 'Afghanistan' (without quotes) field2 - e.g. ' AF' (without quotes but with that space in front)
Is there a way (in SQL) to go through the table and find/replace the whitespace in field2?
-
mulya over 6 yearsAdding my answer as comment to make it more visible: Just to be clear, TRIM by default only remove spaces (not all whitespaces). Here is the doc: dev.mysql.com/doc/refman/5.0/en/…
-
-
TM. over 10 yearsNote: this only removes regular spaces and not other whitespace characters (tab, newline, etc)
-
Evan Byrne about 10 yearsThis should work:
select trim(name) name from test;
-
Chris Sim about 10 yearsyes you're right @TM so it's better to use: UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r','')); etc.
-
DisgruntledGoat about 10 yearsThis removes all tabs/newlines. TRIM should only remove whitespace at either end of the string.
-
mickmackusa about 10 yearsThis has no relevance to the OP.
-
Sankar Ganesh PMP over 9 yearsthis a good idea of thinking and removing the new line characters, thanks , Works like a charm,i have upvoted it , bcoz for this thinking @Chris Sim
-
Oytun over 9 yearsThis should be added as a comment.
-
Timo over 8 yearsWhoah, you're not removing all whitespaces - you are removing everything from the first space onward.
-
Eaten by a Grue about 8 years
TRIM()
works fine for me in aSELECT
statement, I'm really curious as to why this answer's got so many upvotes. Are you using mysql? What version? -
amitchhajer about 8 yearstrim remove leading and trailing spaces only dev.mysql.com/doc/refman/5.7/en/string-functions.html
-
Yashrajsinh Jadeja over 7 yearsThis is not ideal for name containing two words with space separator. Ex:- "Hong Kong" will become "HongKong".
-
Loko over 7 yearsYeah this answer is wrong. How did this get 50+ upvotes?
-
some-non-descript-user over 6 yearsThis is not only wrong, it's dangerous. It can seriously garble someone's data.
-
JoLoCo almost 6 yearsThough @ChrisSim's solution will replace newlines and tabs WITHIN the content too, of course -- which probably isn't what most people want from a TRIM function!
-
Piemol over 4 yearsI downvoted. Testcase:
SELECT CONCAT('"', TRIM(" hello world "), '"') AS `trimmed value` FROM DUAL
gives the wanted output"hello world"
. While the replace variant dangerously removes the space as word separator:SELECT CONCAT('"', REPLACE(" hello world ", ' ', '')) AS `replaced value` FROM DUAL
gives the unwanted output"helloworld"
-
oriadam almost 4 yearsthat's pretty impressive! On MySQL 8+ one can use the new
REGEXP_REPLACE()
-
Maksoud Rodrigues about 2 yearsUPDATE table T1 SET field = (SELECT replace(field, ' ', '') FROM table T2 WHERE T1.id = T2.id); This should work since TRIM doesn't work as expected