Eliminați citatele și virgulele dintr-un șir în MySQL

Importarea unor date dintr-un fișier CSV și numere mai mari decât 1000 se transformă în 1.100 etc.

Ce este o modalitate bună de a elimina atât citatele, cât și virgula de aici, pentru a le putea introduce într-un câmp int ?

Edit:

Datele sunt de fapt deja într-un tabel MySQL, așa că trebuie să fiu capabil să folosesc SQL. Îmi pare rău pentru amestec.

0
fr hi bn

7 răspunsuri

Răspunsul lui Daniel și al lui Eldila are o problemă: elimină toate citatele și virgulele din întregul fișier.

Ceea ce fac de obicei atunci când trebuie să fac ceva de genul asta este să înlocuiesc mai întâi toate citatele de separare și (de obicei) punct și virgulă prin file.

  • Search: ";"
  • Replace: \t

Din moment ce știu în ce coloană valorile mele afectate va fi atunci fac o altă căutare și înlocui:

  • Search: ^([\t]+)\t([\t]+)\t([0-9]+),([0-9]+)\t
  • Replace: \1\t\2\t\3\4\t

... datând valoarea cu virgulă este în coloana a treia.

Trebuie să începeți cu un "^" pentru a vă asigura că începe de la începutul unei linii. Apoi repetați ([0-9] +) \ t ori de câte ori există coloane pe care doriți să le ieșiți doar așa cum sunt.

([0-9] +), ([0-9] +) caută valori unde există un număr, apoi o virgulă și apoi un alt număr.

În șirul de înlocuire folosim \ 1 și \ 2 pentru a păstra doar valorile din linia editată, separându-le cu \ t (tab). Apoi am pus \ 3 \ 4 (fără o filă între) pentru a pune cele două componente ale numărului fără virgulă imediat după celălalt. Toate valorile după aceea vor fi lăsate singure.

Dacă aveți nevoie de fișierul dvs. pentru a avea punct și virgulă pentru a separa elementele, atunci puteți continua și înlocuiți filele cu punct și virgulă. Cu toate acestea, atunci dacă renunțați la citate, va trebui să vă asigurați că valorile textului nu conțin semnele punctului. De aceea prefer să folosesc TAB ca separator de coloane.

De obicei, fac acest lucru într-un editor de text obișnuit (EditPlus) care acceptă RegExp, dar aceleași regexps pot fi folosite în orice limbaj de programare.

0
adăugat

Ai putea folosi această comandă Perl.

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

Este posibil să trebuiască să jucați puțin, dar ar trebui să facă truc.

0
adăugat

Comanda mea elimină toate "," și "" ".

Pentru a transforma stingerea "1.000" mai strictă, veți avea nevoie de următoarea comandă.

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

De fapt, nlucaroni, cazul tău nu este corect. Exemplul dvs. nu include ghilimele duble, deci

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

nu se va potrivi cu regexul meu. Este nevoie de formatul "XXX, XXX". Nu mă pot gândi la un exemplu de când se va potrivi incorect.

Toate exemplul următor nu va include delimitatorul în regex:

  "111111", 234
234, "111111"
„111111“, „111111“
 

Spuneți-mi dacă vă puteți gândi la un contra-exemplu.

Noroc!

0
adăugat

Soluția la întrebarea schimbată este în esență aceeași.

Va trebui să executați o interogare selectată cu regex în cazul în care clauza.

Uite ceva

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

Pentru aceste rânduri, doriți să efectuați următoarea substituție de regex s/"(\ d +), (\ d +)"/$ 1 $ 2/și apoi să actualizați câmpul cu noua valoare.

Te rog, Joseph Pecoraro serios și să ai o copie de rezervă înainte de a face schimbări de masă în orice fișiere sau baze de date. Deoarece ori de câte ori faceți regex, puteți să vă faceți greșeli în cazul în care există cazuri pe care le-ați pierdut.

0
adăugat

Iată un bun caz pentru expresiile regulate. Aveți posibilitatea să executați o căutare și să înlocuiți datele înainte de a importa (mai ușor) sau mai târziu dacă importul SQL a acceptat acele caractere (nu la fel de ușor). Dar, în orice caz, aveți numeroase metode de a găsi și înlocui, fie ele editoare, limbi de scripting, programe GUI etc. Amintiți-vă că veți dori să găsiți și să înlocuiți toate de caractere rele.

A typical regular expression to find the comma and quotes (assuming just double quotes) is: (Blacklist)

/[,"]/

Or, if you find something might change in the future, this regular expression, matches anything except a number or decimal point. (Whitelist)

/[^0-9\.]/

Ceea ce a fost discutat de către cei de mai sus este că nu cunoaștem toate datele din fișierul dvs. CSV. Se pare că doriți să eliminați virgulele și ghilimelele din toate numerele din fișierul CSV. Dar pentru că nu știm ce altceva este în fișierul CSV, vrem să ne asigurăm că nu corupem alte date. Făcând orbește o găsire/înlocuire ar putea afecta alte porțiuni ale fișierului.

0
adăugat

Cred că aici, deoarece datele au putut să importe că câmpul este de fapt un câmp varchar sau un câmp de caractere, deoarece importul într-un câmp numeric ar fi eșuat. Aici a fost un caz de testare am fugit doar o soluție MySQL, SQL.

  1. The table is just a single column (alpha) that is a varchar.

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

    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. Update statement.

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

Deci, în final, declarația pe care am folosit-o a fost:

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

M-am uitat la Documentația MySQL și nu mi se pare că aș putea să fac expresiile regulate găsind și înlocuind . Deși ați putea, ca Eldila , utilizați o expresie regulată pentru o căutare și apoi o soluție alternativă pentru înlocuire.


De asemenea, aveți grijă cu s/"(\ d +), (\ d +)"/$ 1 $ 2/ doriți să faceți o înlocuire globală (în perl, care este s /// g ). Dar chiar și cu o înlocuire globală, înlocuirea începe în locul în care ați rămas ultima dată (cu excepția cazului în care perl este diferit) și ar fi dor de fiecare grup separat de virgule. O soluție posibilă ar fi aceea de a face ca prima (\ d +) opțională ca și s/(\ d +)?, (\ D +)/$ 1 $ 2/g și înlocuiți-o pentru a elimina ghilimelele.

Iată câteva exemple de rubin de expresii regulate care acționează doar pe șirul "1.000.000", observați că NU sunt citate dublă în interiorul șirului, acesta este doar un șir de număr în sine.

>> "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"
0
adăugat
MySQL - comunitatea Română
MySQL - comunitatea Română
19 participanți

Comunitatea română a programatorilor MySQL.