Thread: Merging two columns into one
Hi All, I have two columns in a table (areacode and phone number) that I'd like to merge into one (phone number) containing bothsets of info. Could anyone suggest a simple way of achieving this? Thanks, Gary MacMinn
On Fri, 23 Jun 2000, Gary MacMinn wrote: > Hi All, > > I have two columns in a table (areacode and phone number) that I'd like to merge into one (phone number) containing both sets of info. Could anyone suggest a simple way of achieving this? export the data to a file using the copy command, remove the delimiter with the unix command 'tr -d' import the file, now minus the delimeter using the copy command. -- Sincerely etc., NAME Christopher Sawtell - iOpen Technologies Ltd.CELL PHONE 021 257 4451ICQ UIN 45863470EMAIL chris @ iopen. co . nz, csawtell @ xtra . co . nzWWW http://www.iopen.co.nzCNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Christopher Sawtell wrote: >On Fri, 23 Jun 2000, Gary MacMinn wrote: >> Hi All, >> >> I have two columns in a table (areacodeand phone number) that I'd like to > merge >into one (phone number) containing both sets of info. Could anyonesuggest a >simple way of achieving this? > >export the data to a file using the copy command, >remove the delimiterwith the unix command 'tr -d' >import the file, now minus the delimeter using the copy command. Surely tr will remove ALL the delimiters, so this is not helpful unless these columns are the only ones in the table. You would have to use awk or perl to process the exported file and delete the correct delimiter. For an SQL solution, how about: SELECT col1, col2,..., areacode || phone as phone, colx, coly,... INTO new_table FROM table; Then you can drop the old table and recreate it with the correct columns and import the data into it from new_table. (You could just rename new_table if it doesn't need to be created with constraints.) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Train up a child in the way he should go, and when he is old, he will notdepart from it." Proverbs 22:6
Thus spake Gary MacMinn
> I have two columns in a table (areacode and phone number) that I'd like to merge into one (phone number) containing
bothsets of info. Could anyone suggest a simple way of achieving this?
Oliver showed you how to merge these together with the SQL concatenate
operator but there is another possibility. PostgreSQL could use a
phone number type. I have been meaning to write a user defined type
for some time but never seem to get around to it. Maybe you could
give this a shot. The examples in contrib should get you started.
Once finished you can make the phone # one column and split out parts
with various functions. Don't forget to have a place for country code
and extension.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Thus spake Oliver Elphick
> SELECT col1, col2,..., areacode || phone as phone, colx, coly,...
Although you may want this for easier reading.
SELECT col1, col2,..., (areacode || ' ') || phone as phone, colx, coly,...
Also, I would do "as fullphone" instead so that I don't get confused
between the field name and my generated string but that's mainly a
style issue.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
"D'Arcy J.M. Cain" wrote: > > PostgreSQL could use a > phone number type. I have been meaning to write a user defined type > for some time but never seem to get around to it. Maybe you could > give this a shot. The examples in contrib should get you started. > Once finished you can make the phone # one column and split out parts > with various functions. Don't forget to have a place for country code > and extension. i messed around with one a while back, but i dropped it before i finished it. if i recall correctly, it was mostly working, but i don't know if i broke it again after that or not. i'll put a copy of it on my web site -- you can download it, clean it up and put it in contrib if you want. i just tarred up the files, there's no documentation for it & barely any copyright info in it. put whatever license you want on it if you want to reuse it. the files located at: http://www.potlatch.org/source/phone.tar.gz -- Jeff Hoffmann PropertyKey.com
All, Many thanks for your thoughts on the merging of columns. The tr method would have killed all the delimiters as a few peoplenoted. The || method in SQL was the winner, although the final table this has to be done to (4 million records) shouldkill the system for a while!!!!! Must make sure I've got enough disk space to do it all as well. Maybe doing it a sectionat a time would be in order. Thanks to all, Gary MacMinn