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

Ответ 1

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

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

/[,"]/

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

/[^0-9\.]/

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

Ответ 2

Мое предположение заключается в том, что, поскольку данные были способны импортировать, что поле на самом деле является varchar или некоторым символьным полем, поскольку импорт в числовое поле может быть неудачным. Здесь был тестовый сценарий, в котором я запускал чисто решение MySQL, SQL.

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

  mysql> desc t;
  
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | alpha | varchar(15) | YES  |     | NULL    |       | 
  +-------+-------------+------+-----+---------+-------+
  

• Добавить запись

  mysql> insert into t values('"1,000,000"');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from t;
  
  +-------------+
  | alpha       |
  +-------------+
  | "1,000,000" | 
  +-------------+
  

• Операция обновления.

  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, и это выглядело не так, как я мог бы найти и заменить регулярные выражения. Хотя вы можете, например Eldila, использовать регулярное выражение для поиска, а затем альтернативное решение для замены.

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

Вот некоторые примеры ruby ​​регулярных выражений, действующих только на строку "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"

Ответ 3

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

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

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

Ответ 4

Здесь путь PHP:

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

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

Ответ 5

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

Чтобы более точно преобразовать sting "1,000", вам понадобится следующая команда.

Perl -lne 's/"(\d+),(\d+)"/$1$2/; print' file.txt > newfile.txt

Ответ 6

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

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

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

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

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

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

Ура!

Ответ 7

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

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

Somthing like

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

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

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

Ответ 8

У Daniel и Eldila есть одна проблема: они удаляют все кавычки и запятые во всем файле.

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

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

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

• Поиск: ^ ([\ 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, но те же самые регулярные выражения могут использоваться на любом языке программирования.