MySQL column: Change existing phone numbers into specific format? -

-

i have mysql column contains phone numbers, problem they're in different formats, such as:

  • 2125551212
  • 212-555-1212
  • (212)5551212

i'd know if it's possible take existing 10 digits, remove formatting, , change them format: (212) 555-1212

not duplicate, i'm looking update several thousand entries instead of masking new entries.

unfortunately, no regexp_matches() or translate() function comes standard mysql installation (they postgres), way find dirty, works.

  1. first cleanse column removing characters aren't numbers using replace()
  2. then take several parts of string separate them out using substr()
  3. finally, concatenate them adding symbols between substrings concat()

if have more characters need truncate, add replace() on top of 3 existing.

sample data

create table nums ( num text );  insert nums values      ('2125551212'),     ('212-555-1212'),     ('(212)5551212');

query formatting data

select    num,    concat('(',substr(num_cleansed,1,3),') ',substr(num_cleansed,4,3),'-',substr(num_cleansed,7)) num_formatted (   select      num,      replace(replace(replace(num,'(',''),')',''),'-','') num_cleansed   nums   ) foo

result

num             num_formatted 2125551212      (212) 555-1212 212-555-1212    (212) 555-1212 (212)5551212    (212) 555-1212

click here sqlfiddle preview output.

i'm leaving update statement homework reader.


Comments

Post a Comment

Popular posts from this blog

how to insert data php javascript mysql with multiple array session 2 -

-
i have method method discussed , successful data entered 1 when more 1 can not database this programs my php form : <form class="form-horizontal" id="form-produk"> <div class="control-group" > <label class="control-label" for="kelas">kategori</label> <div class="controls"> <select class='form-control' name='id_kategori'> <?php $tampil=mysql_query("select * kategori order nama_kategori"); if ($id_kat==0){ echo "<option value=0 selected>- pilih kategori -</option>"; } while($w=mysql_fetch_array($tampil)){ if ($id_kat==$w[id_kategori]){ echo "<option value=$w[id_kategori] selected>$w[nama_kategori]</option>"; } else{ echo "<option value=$w[id_kategori]>$...
Read more

multithreading - Exception in Application constructor -

-
i wanna write snake game javafx , there exception don't know , want know how fix it. ( know it's not complete yet ) and want know , class extends application ( start override ) main in other programs? see , here not static void main bc didn't need, if want add main shoud do? it's exeption... exception in application constructor exception in thread "main" java.lang.nosuchmethodexception: main_snake.main([ljava.lang.string;) @ java.lang.class.getmethod(class.java:1819) @ com.intellij.rt.execution.application.appmain.main(appmain.java:125) and code : import javafx.animation.animationtimer; import javafx.application.application; import javafx.event.eventhandler; import javafx.scene.scene; import javafx.scene.canvas.canvas; import javafx.scene.canvas.graphicscontext; import javafx.scene.input.keyevent; import javafx.scene.layout.borderpane; import javafx.scene.paint.color; import javafx.stage.stage; import java.util.arraylist; /** * creat...
Read more

windows - CertCreateCertificateContext returns CRYPT_E_ASN1_BADTAG / 8009310b -

-
i realize similar post others (e.g. this one ), there details missing posts might significant case. to start with, here's simplified program: #include "stdafx.h" #include <windows.h> #include <wincrypt.h> int _tmain(int argc, _tchar* argv[]) { // usage: certextract certpath char keyfile[] = "c:\\certificates\\public.crt"; byte lp[65536]; security_attributes sa; handle hkeyfile; dword bytes; pccert_context certcontext; sa.nlength = sizeof(sa); sa.lpsecuritydescriptor = null; sa.binherithandle = false; hkeyfile = createfile(keyfile, generic_read, file_share_read, &sa, open_existing, file_attribute_normal, null); if (hkeyfile) { if (readfile(hkeyfile, lp, getfilesize(hkeyfile, null), &bytes, null) && bytes > 0) { certcontext = certcreatecertificatecontext(x509_asn_encoding, lp, bytes); if (certcontext) { printf("yay...
Read more