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

On 16/06/2012, Jeff Davis <pgsql(at)j-davis(dot)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?