[SQL] Need a help in regexp
Hi,
Need a help in regexp!
I have a table in which the data's are entered like,
Example:
One (1)
Two (2)
Three (3)
I want to extract the data which is only within the parentheses.
that is
1
2
3
i have written a query,
*select regexp_matches(name,'([^(]+)([)]+)','g') from table;*
which outputs the data as,
{"test"}
{"test2"}
Thank You
Nicholas I
[SQL] help
Hi, I have a table in which the data's are entered like, Example: One (1) Two (2) Three (3) I want to extract the data which is only within the parentheses. that is 1 2 3 Thank You Nicholas I
[SQL] insert into help
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. -Nicholas I
[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
[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
[SQL] Need help on update.
Hi, there are two tables, table1 and table2, each having same column name called sn_no,name. i want to update table1 names with table2 where sn_no are same. select * from table1; sn_no | name ---+--- 1 | ramnad 2 | bangalore 3 | chennai select * from table2; sn_no | name ---+--- 1 | Hyderabad 2 | Delhi 3 | Bombay Any help ? I tried with , some of the queries like, *UPDATE table1 SET name = (select name from table2) where table2.sn_no = table1.sn_no;* ERROR: missing FROM-clause entry for table "table2" LINE 1: ...table1 SET name = (select name from table2) where table2.sn_. *UPDATE table1 inner join table2 on table2.sn_no = table1.sn_no set table2.name = table1.name;* ERROR: syntax error at or near "inner" LINE 1: UPDATE table1 inner join table2 on table2.sn_no = table1.sn_... -Nicholas I
Re: [SQL] Need help on update.
that was amazing, it worked thanks a lot. -Nicholas I On Thu, Oct 21, 2010 at 1:40 PM, Richard Huxton wrote: > On 21/10/10 08:43, Nicholas I wrote: > >> Hi, >> >> there are two tables, table1 and table2, each having same column name >> called sn_no,name. i want to update table1 names with table2 where sn_no >> are same. >> >> select * from table1; >> sn_no | name >> ---+--- >> 1 | ramnad >> 2 | bangalore >> 3 | chennai >> >> >> select * from table2; >> sn_no | name >> ---+--- >> 1 | Hyderabad >> 2 | Delhi >> 3 | Bombay >> >> Any help ? >> >> I tried with , some of the queries like, >> > > Close. This is surprisingly difficult in standard SQL. PostgreSQL has a > (non-standard) FROM clause you can use though. > > BEGIN; > > CREATE TABLE table1 (sn int, nm text); > CREATE TABLE table2 (sn int, nm text); > INSERT INTO table1 VALUES (1,'ramnad'),(2,'bangalore'),(3,'chennai'); > INSERT INTO table2 VALUES (1,'Hyderabad'),(2,'Delhi'),(3,'Bombay'); > > UPDATE table1 SET nm = table2.nm > FROM table2 > WHERE table1.sn = table2.sn; > > SELECT * FROM table1; > > ROLLBACK; > > Be careful with aliasing the target of the update (table1 in this case). As > another poster has discovered, that counts as another table in your join. > > -- > Richard Huxton > Archonet Ltd >
[SQL] Insert a space between each character
Hi, can anybody help me, to insert a space between each character in postgresql. for example, ABC output A B C -Dominic
[SQL] Table to Excel
Hi , Does any one how to convert table to excel. I just tried the COPY table_name to '/home/user/output.xls' using delimiters ','; but the out put of the table that is the column in a table are tab separted, all were merged in a single cell. -Nicholas I
[SQL] Comparing two tables of different database
Hi, can anybody me suggest me, how to compare two tables of different database. -Nicholas I
Re: [SQL] Comparing two tables of different database
Hi All, For example, There are two database. database1 and database 2; database1 has a table called pr_1 with the columns, id,name and time. database2 has a table called sr_1 with the_columns id,name and time. i would like to find out the differences that is, find the names that are not in sr_1 but in pr_1. we can achieve this by the query, select name from sr_1 where name not in (select name from pr_1); the above query will work in case of two tables in the same database. But the problem is, these two tables are in different database. i did not understand about the dblink. is there any exaples on dblink. can we do it without using dblink. -Nicholas I On Thu, Apr 30, 2009 at 9:07 AM, Joshua Tolley wrote: > On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote: > >The simple answer is to pg_dump both tables and compare the output > with > >diff. > >Other than that, I think you'll need a custom program. > > For all but the strictest definition of "identical", that won't work. > Tables may easily contain the same information, in different on-disk > order, and pg_dump will most likely give the data to you in an order > similar to its ordering on disk. > > Something like a COPY () TO , where includes an > ORDER BY clause, might give you a suitable result from both tables, on > which you could then take a checksum. > > - Josh / eggyknap > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkn5HQoACgkQRiRfCGf1UMPcagCfQDRa2bXPRjgSuVsrFYTnGTTC > rhoAnAlGwp0vSKd2uspyFvxCTfugG6Yh > =LO6r > -END PGP SIGNATURE- > >
