[SQL] find and replace the string within a column
Hi, the data in my table, have a substring enclosed in parenthesis, 1. i want to replace the string with the brackets to null or any other value. 2. remove the contents within brackets. Example: table name person: name Samuel (S/o Sebastin ) - remove the word within the brackets. the output should be , Samuel. the below one help's me to find the data within the two brackets. SELECT name,(REGEXP_MATCHES(name, E'\\(.+?\\)'))[1] from person; regexp_matches (S/o Sebastin ) - -Nicholas I
Re: [SQL] find and replace the string within a column
Hello, > the below one help's me to find the data within the two brackets. > > SELECT name,(REGEXP_MATCHES(name, E'\\(.+?\\)'))[1] from person; > regexp_matches > > (S/o Sebastin ) > - > Trying to work with your code - update table set name = substr( name,1,strpos(name, (REGEXP_MATCHES(name, E'\\(.+?\\)')) )-1 ) || substr( name,strpos(name, (REGEXP_MATCHES(name, E'\\(.+?\\)')) ) + 1 ,char_length(name)) I am trying to find what is there before the pattern and after the pattern and concatenating them . Please see documentation for proper use of substr,strpos,cahr_length etc. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] insert into help
Hello Guillaume Lelarge !
I suggest you try the following question:
RE-CREATE YOUR TABLES AS FOLLOW:
CREATE SEQUENCE table1_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 1000
START 1
CACHE 1;
CREATE TABLE TABLE1 (
ID INTEGER NOT NULL DEFAULT nextval('table1_id_seq'::regclass)
, NAME VARCHAR(200) NOT NULL
);
CREATE TABLE TABLE2 (
NAME VARCHAR(200) NOT NULL
);
INSERTING THE DATA--
INSERT INTO TABLE1 (NAME) SELECT NAME FROM TABLE2;
Note: The ID in Table1 will be generated automaticale because of
DEFAULT nextval('table1_id_seq'::regclass)
James Kitambara Computer System Analyst and Programmer
Bank of Tanzania,
P.O. Box 2939, Mobile : +255 71 3307632, Dar es Salaam, Tanzania.
--- On Wed, 22/9/10, Guillaume Lelarge wrote:
From: Guillaume Lelarge
Subject: Re: [SQL] insert into help
To: "Nicholas I"
Cc: pgsql-sql@postgresql.org
Date: Wednesday, 22 September, 2010, 8:35
Le 22/09/2010 09:32, Nicholas I a écrit :
> Hi,
>
> i have two tables,
> ---
> *table1
>
> id type serial, name varchar;*
> *--
> table 2
>
> name varchar;*
> ---
>
> i want to insert the values of table 2 into table 1, with automatic id's.
>
> insert into table1(select * from table2);
>
> is not working, how can i append the data to table 1 with auto incremented
> or nextval.
>
INSERT INTO table1 (name) SELECT name FROM table2;
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] identifying duplicates in table with redundancies
Howdy, Tarlika. First, did you past correctly your query into your mail? I am asking this because your query doesn't seem work for me, it returns an empty list :-| Your most nested query, this one, -- distinct trainer id-name select distinct on (trainer_id,trainer_name) trainer_id as id, trainer_name as name from student returns this 1|"MacDonald" 2|"Smith" 3|"Smith" Then, your next most nested query, which includes the previous one, -- trainer ids with appearing with different names select distinct on (id) id from ( -- distinct trainer id-name select distinct on (trainer_id,trainer_name) trainer_id as id, trainer_name as name from student ) as trainer group by trainer.id having count (trainer.name) > 1 will group by trainer_id and look for trainer_ids which appear more than once, and, as you can see from the above listing there is none... So, I'm asking if you typed it correctly on mail, because it doesn't seem to work, at least not for me 1) multiples trainer names for same trainer id 2) multiple trainer ids for same trainer name To achieve 2) I would use this query SELECT DISTINCT trainer_id,trainer_name FROM ( SELECT trainer_name -- The field you want to test for duplicates FROM ( SELECT DISTINCT "trainer_id","trainer_name" FROM student ) x GROUP BY "trainer_name"-- the field you want to test for duplicates HAVING (COUNT(*) > 1) ) z NATURAL JOIN student y It will give you a list of the trainer names who have more than one trainer ID and the respective trainer IDS. For your particular example data result will be 2|"Smith" 3|"Smith" As Smith is the only trainer with records with diferent trainer IDs. Question : Can this be what you want? You can add ORDER BY clauses in the end as you wish to order the list in any way you'd like. If you want to achieve 1) just substitute the trainer_name by trainer_id on the commented places. But on the example data you provided you don't have the 1) situation, am I right? So it will output an empty list. Best, Oliver - Original Message - From: "Tarlika Elisabeth Schmitz" To: Sent: Thursday, September 23, 2010 10:39 PM Subject: [SQL] identifying duplicates in table with redundancies I loaded data from a spread into a interim table so I can analyze the quality of the data. The table contains an entry for every student (250K records) and his trainer. Eventually, I want to extract a unique list of trainers from it. But first of all I want to check for duplicates: 1) multiples trainer names for same trainer id 2) multiple trainer ids for same trainer name I cobbled together the SQL and it does the job but it seems rather convoluted. I would like to know how I can improve it. CREATE TABLE student ( id INTEGER NOT NULL, name VARCHAR(256) NOT NULL, trainer_id INTEGER, trainer_name VARCHAR(256), ); EXAMPLE DATA 22 John 1 Macdonald 23 Jane 1 MacDonald 24 Paul 1 MacDonald 25 Dick 2 Smith 26 Bill 3 Smith 27 Kate 3 Smith -- outputs trainer ids which appear under different names select trainer_id, trainer_name from ( -- different id/name combinations select distinct on (trainer_name) trainer_id, trainer_name from student where trainer_id in ( -- trainer ids with appearing with different names select distinct on (id) id from ( -- distinct trainer id-name select distinct on (trainer_id,trainer_name) trainer_id as id, trainer_name as name from student ) as trainer group by trainer.id having count (trainer.name) > 1 ) ) as y order by trainer_id -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] find and replace the string within a column
On 24/09/2010 5:15 PM, Nicholas I wrote: Example: table name person: name Samuel (S/o Sebastin ) - remove the word within the brackets. the output should be , Samuel. Can't you just regexp_replace, matching \([^)]+\) (in other words "an open parenthisis, then a sequence of one or more of any character other than a close parenthesis, followed by a close parentheis) and replacing with an empty string ? -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pgdump with insert command help
hi, i am trying to dump a table with insert command rather tahn copy. pg_dump -Dt --insert table dbname > table.sql; i am not able to get the output. is this correct ? -Nicholas I
Re: [SQL] pgdump with insert command help
Le 24/09/2010 14:32, Nicholas I a écrit : > hi, > > i am trying to dump a table with insert command rather tahn copy. > > pg_dump -Dt --insert table dbname > table.sql; > > i am not able to get the output. is this correct ? > > > -Nicholas I > You should put the table name right after the -t command-line option. This way: pg_dump -Dt table --insert dbname > table.sql -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] find and replace the string within a column
On Friday 24. September 2010 13.34.12 Craig Ringer wrote:
> On 24/09/2010 5:15 PM, Nicholas I wrote:
>
> > Example:
> > table name person:
> >
> > name
> >
> > Samuel (S/o Sebastin )
> > -
> >
> > remove the word within the brackets.
> > the output should be , Samuel.
>
> Can't you just regexp_replace, matching \([^)]+\) (in other words "an
> open parenthisis, then a sequence of one or more of any character other
> than a close parenthesis, followed by a close parentheis) and replacing
> with an empty string ?
I'm doing a similar task, removing comments "hidden" within curly braces like
this:
str := REGEXP_REPLACE(str, '{.*?}', '', 'g');
No escaping needed at all.
regards,
Leif Biberg Kristensen
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] identifying duplicates in table with redundancies
Dear Oliveiros, Thank you for taking the time to help. On Fri, 24 Sep 2010 11:22:21 +0100 "Oliveiros d'Azevedo Cristina" wrote: >- Original Message - >From: "Tarlika Elisabeth Schmitz" >To: >Sent: Thursday, September 23, 2010 10:39 PM >Subject: [SQL] identifying duplicates in table with redundancies > > >>[...] I want to check for duplicates: >> >> 1) multiples trainer names for same trainer id >> 2) multiple trainer ids for same trainer name >> >> I cobbled together the SQL and it does the job but it seems rather >> convoluted. I would like to know how I can improve it. >> >> CREATE TABLE student ( >> id INTEGER NOT NULL, >> name VARCHAR(256) NOT NULL, >> trainer_id INTEGER, >> trainer_name VARCHAR(256), >> ); >> >> >> EXAMPLE DATA >> >> 22 John 1 Macdonald >> 23 Jane 1 MacDonald >> 24 Paul 1 MacDonald >> 25 Dick 2 Smith >> 26 Bill 3 Smith >> 27 Kate 3 Smith >> >> >> select trainer_id, trainer_name from >> ( >>select distinct on (trainer_name) trainer_id, trainer_name >>from student >>where trainer_id in >>( >>select distinct on (id) id >>from >>( >>select distinct on (trainer_id,trainer_name) >> trainer_id as id, >>trainer_name as name from student >>) as trainer >>group by trainer.id >>having count (trainer.name) > 1 >>) >> ) as y >> order by trainer_id >Howdy, Tarlika. > >First, did you past correctly your query into your mail? >I am asking this because your query doesn't seem work for me, it >returns an empty list :-| > >Your most nested query, [...] >returns this > >1|"MacDonald" >2|"Smith" >3|"Smith" For me, the innermost query returns: 1|"Macdonald" 1|"MacDonald" 2|"Smith" 3|"Smith" (note the lower/uppercase "d" in MacDonald) The whole query returns: 1|"Macdonald" 1|"MacDonald" >> 1) multiples trainer names for same trainer id >> 2) multiple trainer ids for same trainer name > >To achieve 2) I would use this query > >SELECT DISTINCT trainer_id,trainer_name >FROM ( >SELECT trainer_name -- The field you want to test for duplicates >FROM ( >SELECT DISTINCT "trainer_id","trainer_name" >FROM student >) x >GROUP BY "trainer_name"-- the field you want to test for duplicates >HAVING (COUNT(*) > 1) >) z >NATURAL JOIN student y I see my 2 innermost queries are the same as yours, just a bit more wordy. I messed up at the third query, which threw up an error when I tried to add an ORDER BY. >It will give you a list of the trainer names who have more than one >trainer ID and the respective trainer IDS. >For your particular example data result will be >2|"Smith" >3|"Smith" Splendid! Just what I wanted. >As Smith is the only trainer with records with diferent trainer IDs. >Question : Can this be what you want? The real table has 25 entries and quite a few dups. >If you want to achieve 1) just substitute the trainer_name by >trainer_id on the commented places. 1) works as well now - just had to transpose id/name. -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] identifying duplicates in table with redundancies
Hey,Tarlika. I tried to reproduce your test case through a series of inserts and It seems that the lower case "d" went unnoticed. That explains the empty list i got as result. My fault. Sorry :-( Great to hear it helped you Best, Oliveiros Enviado de meu iPhone Em 24/09/2010, às 05:12 PM, "Tarlika Elisabeth Schmitz" e> escreveu: Dear Oliveiros, Thank you for taking the time to help. On Fri, 24 Sep 2010 11:22:21 +0100 "Oliveiros d'Azevedo Cristina" wrote: - Original Message - From: "Tarlika Elisabeth Schmitz" To: Sent: Thursday, September 23, 2010 10:39 PM Subject: [SQL] identifying duplicates in table with redundancies [...] I want to check for duplicates: 1) multiples trainer names for same trainer id 2) multiple trainer ids for same trainer name I cobbled together the SQL and it does the job but it seems rather convoluted. I would like to know how I can improve it. CREATE TABLE student ( id INTEGER NOT NULL, name VARCHAR(256) NOT NULL, trainer_id INTEGER, trainer_name VARCHAR(256), ); EXAMPLE DATA 22 John 1 Macdonald 23 Jane 1 MacDonald 24 Paul 1 MacDonald 25 Dick 2 Smith 26 Bill 3 Smith 27 Kate 3 Smith select trainer_id, trainer_name from ( select distinct on (trainer_name) trainer_id, trainer_name from student where trainer_id in ( select distinct on (id) id from ( select distinct on (trainer_id,trainer_name) trainer_id as id, trainer_name as name from student ) as trainer group by trainer.id having count (trainer.name) > 1 ) ) as y order by trainer_id Howdy, Tarlika. First, did you past correctly your query into your mail? I am asking this because your query doesn't seem work for me, it returns an empty list :-| Your most nested query, [...] returns this 1|"MacDonald" 2|"Smith" 3|"Smith" For me, the innermost query returns: 1|"Macdonald" 1|"MacDonald" 2|"Smith" 3|"Smith" (note the lower/uppercase "d" in MacDonald) The whole query returns: 1|"Macdonald" 1|"MacDonald" 1) multiples trainer names for same trainer id 2) multiple trainer ids for same trainer name To achieve 2) I would use this query SELECT DISTINCT trainer_id,trainer_name FROM ( SELECT trainer_name -- The field you want to test for duplicates FROM ( SELECT DISTINCT "trainer_id","trainer_name" FROM student ) x GROUP BY "trainer_name"-- the field you want to test for duplicates HAVING (COUNT(*) > 1) ) z NATURAL JOIN student y I see my 2 innermost queries are the same as yours, just a bit more wordy. I messed up at the third query, which threw up an error when I tried to add an ORDER BY. It will give you a list of the trainer names who have more than one trainer ID and the respective trainer IDS. For your particular example data result will be 2|"Smith" 3|"Smith" Splendid! Just what I wanted. As Smith is the only trainer with records with diferent trainer IDs. Question : Can this be what you want? The real table has 25 entries and quite a few dups. If you want to achieve 1) just substitute the trainer_name by trainer_id on the commented places. 1) works as well now - just had to transpose id/name. -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] identifying duplicates in table with redundancies
Hello Oliveiros, On Fri, 24 Sep 2010 18:12:18 +0100 Oliver d'Azevedo Christina wrote: >Hey,Tarlika. >I tried to reproduce your test case through a series of inserts and >It seems that the lower case "d" went unnoticed. I can assure you people up here (in Scotland) get quite upset about it! It took me a while to get my head round your outer query with the NATURAL JOIN between the student table and the nested query results. I have done table joins before but this solution would not have sprung to mind. -- Many thanks, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
