Удалить кавычки и запятые из строки в MySQL

Я импортирую некоторые данные из CSVфайла и числа, которые больше, чем 1000получаются 1,100и т. Д.

Какой хороший способ удалить из этого кавычки и запятую, чтобы я мог поместить это в intполе?

Редактировать:

Данные на самом деле уже находятся в таблице MySQL, поэтому мне нужно быть в состоянии использовать SQL. Извините за путаницу.

11.08.2008 16:28:57
8 ОТВЕТОВ
РЕШЕНИЕ

Вот хороший случай для регулярных выражений. Вы можете выполнить поиск и замену данных либо до импорта (проще), либо позже, если импорт SQL принял эти символы (не так просто). Но в любом случае у вас есть любое количество методов для поиска и замены, будь то редакторы, языки сценариев, программы с графическим интерфейсом и т. Д. Помните, что вы захотите найти и заменить все плохие символы.

Типичное регулярное выражение для поиска запятой и кавычек (при условии, что используются только двойные кавычки): (черный список)

/[,"]/

Или, если вы обнаружите, что в будущем что-то может измениться, это регулярное выражение будет соответствовать чему угодно, кроме числа или десятичной точки. (Белый список)

/[^0-9\.]/

Люди, о которых говорилось выше, говорили о том, что мы не знаем всех данных в вашем CSV-файле. Похоже, вы хотите удалить запятые и кавычки из всех чисел в файле CSV. Но поскольку мы не знаем, что еще находится в файле CSV, мы хотим убедиться, что мы не повредим другие данные. Слепое выполнение поиска / замены может повлиять на другие части файла.

2
11.08.2008 19:21:10

Вы можете использовать эту команду Perl.

Perl -lne 's/[,|"]//; print' file.txt > newfile.txt

Возможно, вам придется немного поиграть с этим, но это должно сработать.

0
11.08.2008 16:44:23

Вот способ PHP:

$stripped = str_replace(array(',', '"'), '', $value);

Ссылка на страницу W3Schools

0
11.08.2008 16:50:12

Ответ Дэниела и Эльдила имеет одну проблему: они удаляют все кавычки и запятые во всем файле.

Что я обычно делаю, когда мне нужно сделать что-то подобное, - это сначала заменить все разделяющие кавычки и (обычно) точки с запятой на вкладки.

  • Поиск: ";"
  • Заменить: \ т

Так как я знаю, в каком столбце будут мои затронутые значения, я тогда выполню другой поиск и замену:

  • Поиск: ^ ([\ t] +) \ t ([\ t] +) \ t ([0-9] +), ([0-9] +) \ t
  • Заменить: \ 1 \ t \ 2 \ t \ 3 \ 4 \ t

... учитывая значение с запятой в третьем столбце.

Вам нужно начать с «^», чтобы убедиться, что он начинается в начале строки. Затем вы повторяете ([0-9] +) \ t так часто, как есть столбцы, которые вы просто хотите оставить, как есть.

([0-9] +), ([0-9] +) ищет значения, где есть число, затем запятая, а затем другое число.

В строке замены мы используем \ 1 и \ 2, чтобы просто сохранить значения отредактированной строки, разделяя их с помощью \ t (tab). Затем мы помещаем \ 3 \ 4 (без табуляции между ними), чтобы расположить два компонента числа без запятой сразу после друг друга. Все значения после этого останутся одни.

Если вам нужно, чтобы в вашем файле была точка с запятой для разделения элементов, вы можете продолжить и заменить вкладки точкой с запятой. Однако тогда - если вы пропустите кавычки - вы должны будете убедиться, что текстовые значения не содержат сами точки с запятой. Вот почему я предпочитаю использовать TAB в качестве разделителя столбцов.

Я обычно делаю это в обычном текстовом редакторе (EditPlus), который поддерживает RegExp, но те же регулярные выражения можно использовать на любом языке программирования.

-1
11.08.2008 17:14:04

Моя команда удаляет все «,» и «».

Чтобы более точно преобразовать строчку «1000», вам понадобится следующая команда.

Perl -lne 's/"(\d+),(\d+)"/$1$2/; print' file.txt > newfile.txt
0
9.01.2013 05:50:55

На самом деле нлукарони, ваш случай не совсем правильный. Ваш пример не содержит двойных кавычек, поэтому

id,age,name,...
1,23,phil,

не будет соответствовать моему регулярному выражению Требуется формат «XXX, XXX». Я не могу вспомнить пример, когда он будет соответствовать неправильно.

Весь следующий пример не будет включать разделитель в регулярное выражение:

"111,111",234
234,"111,111"
"111,111","111,111"

Пожалуйста, дайте мне знать, если вы можете придумать контрпример.

Ура!

0
11.08.2008 18:24:42

Решение измененного вопроса в основном то же самое.

Вам нужно будет выполнить запрос select с предложением regex where.

Что-то вроде

Select *
  FROM SOMETABLE
  WHERE SOMEFIELD REGEXP '"(\d+),(\d+)"'

Для каждой из этих строк необходимо выполнить следующую подстановку регулярных выражений s / "(\ d +), (\ d +)" / $ 1 $ 2 /, а затем обновить поле новым значением.

Пожалуйста, Джозеф Пекораро серьезно и сделайте резервную копию, прежде чем делать массовые изменения любых файлов или баз данных. Потому что всякий раз, когда вы выполняете регулярные выражения, вы можете серьезно испортить данные, если есть случаи, которые вы пропустили.

0
11.08.2008 19:45:44

Я предполагаю здесь, что, поскольку данные смогли импортировать, это поле на самом деле является полем varchar или каким-либо символьным полем, потому что импорт в числовое поле мог быть неудачным. Это был тестовый пример, в котором я использовал чисто MySQL, SQL-решение.

  1. Таблица представляет собой один столбец (альфа), который является varchar.

    mysql> desc t;
    
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | alpha | varchar(15) | YES  |     | NULL    |       | 
    +-------+-------------+------+-----+---------+-------+
  2. Добавить запись

    mysql> insert into t values('"1,000,000"');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from t;
    
    +-------------+
    | alpha       |
    +-------------+
    | "1,000,000" | 
    +-------------+
  3. Обновить заявление.

    mysql> update t set alpha = replace( replace(alpha, ',', ''), '"', '' );
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from t;
    
    +---------+
    | alpha   |
    +---------+
    | 1000000 | 
    +---------+

В итоге я использовал следующее утверждение:

UPDATE table
   SET field_name = replace( replace(field_name, ',', ''), '"', '' );

Я посмотрел на документацию по MySQL, и не похоже, чтобы я мог найти и заменить регулярные выражения . Хотя вы, как и Эльдила , можете использовать регулярное выражение для поиска, а затем альтернативное решение для замены.


Также будьте осторожны с s/"(\d+),(\d+)"/$1$2/тем, что если число содержит больше одной запятой, например «1,000,000», вы захотите сделать глобальную замену (в perl, то есть s///g). Но даже с глобальной заменой замена начинается там, где вы в последний раз остановились (если не отличается perl), и пропустит все остальные группы, разделенные запятыми. Возможное решение - сделать первый (\ d +) необязательным, например, так, s/(\d+)?,(\d+)/$1$2/gи в этом случае мне понадобится второй поиск и замена, чтобы убрать кавычки.

Вот некоторые рубиновые примеры регулярных выражений, действующих только на строку «1,000,000», обратите внимание, что в строке нет двойных кавычек, это просто строка самого числа.

>> "1,000,000".sub( /(\d+),(\d+)/, '\1\2' )
# => "1000,000"  
>> "1,000,000".gsub( /(\d+),(\d+)/, '\1\2' )
# => "1000,000"  
>> "1,000,000".gsub( /(\d+)?,(\d+)/, '\1\2' )
# => "1000000"  
>> "1,000,000".gsub( /[,"]/, '' )
# => "1000000"  
>> "1,000,000".gsub( /[^0-9]/, '' )
# => "1000000"
16
23.05.2017 11:54:08