Jak usunąć początkowe i końcowe białe znaki w polu MySQL?

Mam tabelę z dwoma polami (kraje i kody ISO):

Table1

   field1 - e.g. 'Afghanistan' (without quotes)
   field2 - e.g. 'AF'(without quotes)

W niektórych wierszach drugie pole ma białe znaki na początku i / lub końcu, co wpływa na zapytania.

Table1

   field1 - e.g. 'Afghanistan' (without quotes) 
   field2 - e.g. ' AF' (without quotes but with that space in front)

Czy istnieje sposób (w języku SQL), aby przejść przez tabelę i znaleźć / zamienić białe znaki w field2?

Szukasz TRIM .

UPDATE FOO set FIELD2 = TRIM(FIELD2);

Ogólna odpowiedź, którą skomponowałem z twoich odpowiedzi i innych linków i zadziałała dla mnie i napisałem ją w komentarzu to:

 UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r',''));

itp.

Ponieważ trim () nie usuwa wszystkich białych spacji, więc lepiej jest zastąpić wszystkie białe spacje, które chcesz, a niż je przyciąć.

Mam nadzieję, że pomogę Ci w udostępnieniu mojej odpowiedzi :)

Przed użyciem tego rozwiązania zapoznaj się z przypadkiem użycia:

przycinanie nie działa podczas wykonywania zapytania wybierającego

To działa

select replace(name , ' ','') from test;

Chociaż tak nie jest

select trim(name) from test;

Dla jasności, TRIM domyślnie usuwa tylko spacje (nie wszystkie białe spacje). Oto dokument: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

Wygląda na to, że żadna z bieżących odpowiedzi nie usunie w rzeczywistości 100% białych znaków z początku i końca łańcucha.

Jak wspomniano w innych postach, domyślnie TRIMusuwane są tylko spacje - nie tabulatory, wypełnienia formularzy itp. Kombinacja TRIMznaków określająca inne białe znaki może zapewnić ograniczoną poprawę, np TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM TRIM(BOTH '\f' FROM TRIM(BOTH '\t' FROM TRIM(txt))))). Ale problem z tym podejściem polega na tym, że tylko jeden znak można określić dla konkretnego, TRIMa znaki te są usuwane tylko z początku i końca. Więc jeśli przycinany ciąg ma coś podobnego \t \t \t \t(tj. Alternatywne spacje i znaki tabulacji), potrzeba TRIMbyłoby więcej s - i w ogólnym przypadku mogłoby to trwać w nieskończoność.

W przypadku lekkiego rozwiązania powinno być możliwe napisanie prostej funkcji zdefiniowanej przez użytkownika (UDF) w celu wykonania zadania poprzez zapętlenie znaków na początku i na końcu ciągu. Ale nie zamierzam tego robić ... ponieważ już napisałem raczej cięższy zamiennik wyrażeń regularnych, który również może wykonać zadanie - i może się przydać z innych powodów, jak opisano w tym wpisie na blogu .

Próbny

Demo online Rextester . W szczególności ostatni wiersz przedstawia niepowodzenie innych metod, ale powodzenie metody wyrażenia regularnego.

Funkcja :

-- ------------------------------------------------------------------------------------
-- 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;

Stosowanie:

SELECT reg_replace(
         reg_replace(txt,
                     '^[[:space:]]+',
                     '',
                     TRUE,
                     1,
                     0),
         '[[:space:]]+$',
         '',
         TRUE,
         1,
         0) AS `trimmed txt`
FROM tbl;

To oświadczenie spowoduje usunięcie i zaktualizowanie zawartości pól w Twojej bazie danych

Aby usunąć spacje po lewej stronie wartości pola

UPDATE table SET pole1 = LTRIM (pole1);

dawny. UPDATE element członkowski SET firstName = LTRIM (firstName);

Aby usunąć spacje po prawej stronie wartości pola

UPDATE table SETfield1 = RTRIM (pole1);

dawny. UPDATE element członkowski SET firstName = RTRIM (firstName);

Musiałem przyciąć wartości w kolumnie klucza podstawowego, która zawierała imię i nazwisko, więc nie chciałem przycinać wszystkich białych znaków, ponieważ usuwałoby to spację między imieniem i nazwiskiem, którą musiałem zachować. To, co zadziałało dla mnie, to ...

UPDATE `TABLE` SET `FIELD`= TRIM(FIELD);

lub

UPDATE 'TABLE' SET 'FIELD' = RTRIM(FIELD);

lub

UPDATE 'TABLE' SET 'FIELD' = LTRIM(FIELD);

Zwróć uwagę, że pierwsze wystąpienie FIELD znajduje się w pojedynczych cudzysłowach, ale drugie w ogóle nie jest w cudzysłowie. Musiałem to zrobić w ten sposób lub dał mi błąd składniowy, mówiąc, że był to zduplikowany klucz podstawowy, gdy miałem oba w cudzysłowach.

Jeśli musisz użyć trim w zapytaniu wybierającym, możesz również użyć wyrażeń regularnych

SELECT * FROM table_name WHERE field RLIKE ' * query-string *'

zwraca wiersze z polem takim jak „ciąg zapytania”

możesz użyć ltrim lub rtrim, aby wyczyścić białe znaki z prawej lub lewej strony lub z łańcucha.

Możesz użyć następującego sql, UPDATE TABLESET Column= replace (Column, '', '')

Wiem, że jest już zaakceptowany, ale dla takich jak ja, którzy szukają opcji „usuń WSZYSTKIE białe znaki” (nie tylko na początku i na końcu ciągu):

select SUBSTRING_INDEX('1234 243', ' ', 1);
// returns '1234'

EDYCJA 2019/6/20: Tak, to niedobrze. Funkcja zwraca część ciągu od „kiedy odstęp między znakami wystąpił po raz pierwszy”. Wydaje mi się, że powiedzenie tego usuwa początkowe i końcowe spacje i zwraca pierwsze słowo:

select SUBSTRING_INDEX(TRIM(' 1234 243'), ' ', 1);