Thread: create table from regular expressions applied to rows of multiple tables

Readers,

Tables contains text:

table1
text1, 10
text2, 20
text3, 30
text4, 40

table2
text2, textB 20
text3, textC 30
text5, textD 50
text6, textE 60
text7, textF 70
text8, textG 80

Is it possible to create a new table (table3) using a regular
expression (e.g. ^text*) to select rows which are of the same table
format as table2:

table3
text2, textB 20
text3, textC 30

Thanks in advance.

On Thu, 2012-06-14 at 23:22 +0100, e-letter wrote:
> Readers,
>
> Tables contains text:
>
> table1
> text1, 10
> text2, 20
> text3, 30
> text4, 40
>
> table2
> text2, textB 20
> text3, textC 30
> text5, textD 50
> text6, textE 60
> text7, textF 70
> text8, textG 80
>
> Is it possible to create a new table (table3) using a regular
> expression (e.g. ^text*) to select rows which are of the same table
> format as table2:
>
> table3
> text2, textB 20
> text3, textC 30

I'm not sure I understand exactly what you're asking. Your example
table3 could also be produced by a simple inner join:

   SELECT table1.col1, table2.col2
   FROM table1, table2
   WHERE table1.col1 = table2.col1;

But postgresql also offers a lot of regular expression functions, too.
See:

http://www.postgresql.org/docs/9.2/static/functions-string.html
http://www.postgresql.org/docs/9.2/static/functions-matching.html

Or post a more detailed example to show what you are looking for.

Regards,
    Jeff Davis

On 16/06/2012, Jeff Davis <pgsql@j-davis.com> wrote:
> On Thu, 2012-06-14 at 23:22 +0100, e-letter wrote:
>> Readers,
>>
>> Tables contains text:
>>
>> table1
>> text1, 10
>> text2, 20
>> text3, 30
>> text4, 40
>>
>> table2
>> text2, textB 20
>> text3, textC 30
>> text5, textD 50
>> text6, textE 60
>> text7, textF 70
>> text8, textG 80
>>
>> Is it possible to create a new table (table3) using a regular
>> expression (e.g. ^text*) to select rows which are of the same table
>> format as table2:
>>
>> table3
>> text2, textB 20
>> text3, textC 30
>

I have realised my mistake in explanation. The second table should be:

table2
text2textB, 20
text3textC, 30
text5textD, 50
text6textE, 60
text7textF, 70
text8textG, 80

So the requirement is to be able use a regular expression to: "select
rows that show part of the same string in both tables, but create a
new table formatted as table 2".

> I'm not sure I understand exactly what you're asking. Your example
> table3 could also be produced by a simple inner join:
>
>    SELECT table1.col1, table2.col2
>    FROM table1, table2
>    WHERE table1.col1 = table2.col1;
>

I'm guessing I can try this expression above, but adjusting to use a
regular expression function?