Thread: How to merge several attributes and Delete an attribute
Hello all, My initial table has 3 attributes: FirstName, MiddleName and LastName. Now I want to merge them into one, then I could make a search action using LIKE more easily. what should i do for that? Also, I want to know how to delete one attribute from a table. Thanks a lot, Annie _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx
> Hello all, > > My initial table has 3 attributes: FirstName, MiddleName and > LastName. Now I > want to merge them into one, then I could make a search action using LIKE > more easily. > what should i do for that? You add a new attribute and then run an update like this: update table set newcol = FirstName||" "||MiddleName||" "||LastName; But why do that??? Just do your like query as follows: select * from table where FirstName like 'blah' or MiddleName like 'blah' or LastName like 'blah'; > Also, I want to know how to delete one attribute from a table. You cannot delete attributes in postgres - you just have to ignore them. (Hint - 7.3 anyone...?) chris
On 31 Jan 2002, Christopher Kings-Lynne wrote: > update table set newcol = FirstName||" "||MiddleName||" "||LastName; > > But why do that??? Just do your like query as follows: > > select * from table where FirstName like 'blah' or MiddleName like 'blah' or > LastName like 'blah'; or... select * from table where FirstName||" "||MiddleName||" "||LastName like 'blah'; -- Tod McQuillin
Well, I am not familiar with functions, Do they have better performance than the plain query as the following? select * from table where Firstname like 'blah' or middlename like 'blah'.... Thank you Annie >From: Vladimir Terziev <vladimirt@rila.bg> >To: "Yan Bai" <annie_job@hotmail.com> >Subject: Re: [SQL] How to merge several attributes and Delete an attribute >Date: Thu, 31 Jan 2002 10:21:31 +0200 > > Hi, > I sugest you to use 'textcat' function. Something like this: > select textcat(textcat(textcat(textcat($1, " "), $2), " "), $3) > ' language 'SQL'; > > select * from your_table where textcat(textcat(textcat(textcat(FirstName, >' '), MiddleName), ' '), LastName) like '%somename%'; > > Good luck! > > Vladimir > > >On Thu, 31 Jan 2002 07:34:40 +0000 >"Yan Bai" <annie_job@hotmail.com> wrote: > > > Hello all, > > > > My initial table has 3 attributes: FirstName, MiddleName and LastName. >Now I > > want to merge them into one, then I could make a search action using >LIKE > > more easily. > > what should i do for that? > > > > Also, I want to know how to delete one attribute from a table. > > > > Thanks a lot, > > > > Annie > > > > _________________________________________________________________ > > MSN Photos is the easiest way to share and print your photos: > > http://photos.msn.com/support/worldwide.aspx > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly _________________________________________________________________ Join the world�s largest e-mail service with MSN Hotmail. http://www.hotmail.com
Annie, > Well, I am not familiar with functions, Do they have better > performance than the plain query as the following? > > select * from table where Firstname like 'blah' or middlename like > 'blah'.... The problem with the approach above is that it allows you to find"Annie" or "Bai" but not "Annie Bai". The concatinationapproach,while slower to execute, allows you to search on any combination. Youwill also want to investigatePostgreSQL Regex comparisons so that youcan do case-insensitive similarity matching. Overall, though, you need to build your basic database design knowledgebefore going further on this project. I'd recommend"Database DesignFor Mere Mortals.", followed by any number of introdcutory texts onPostgresql. See http://techdocs.postgresql.org/bookreviews.php Finally, your questions are more appropriate to the NOVICE list; wouldyou be kind enough to post them there, instead? TheSQL list is morefor advanced SQL questions (and I answer questions on both lists!). -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco