Change existing values

[MrCBofBCinTX]

I'm new to this but so far what I'm doing seems to be working okay.
I have some useful data that was entered into a "junk" table that I was using as I learned PostgreSQL.
Someone else entered some of the data and didn't realize that in the following entry:

Millstead - 2 In. x 6 In. x 12 Ft. Dimensional Yellow Pine Lumber

The following are not useful in this column. (Only Yellow Pine IS useful, but in another column)

Millstead - 2

and

 Dimensional Yellow Pine Lumber

(These are not very many entries, but I want to learn from this now)

I can remove these through perl dbi.
I was wondering if these types of changes can made with pure SQL, or if it has to be done through something like perl?

I want

2 In. x 6 In. x 12 Ft.

as end result.

 

[feherke]

Hi

Probably can be done without programming, just executing the SQL statements in [tt]psql[/tt].

But I not understand your rule, so I can not suggest how to write your [tt]update[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[MrCBofBCinTX]

What i am asking is:

I have an entry, xyz
I want to know if I can remove x and z, but leave y in updated entry.

Can this process be done directly in just psql, or only through external programming?

I saw a lot of stuff in the advanced section of the manual that I didn't understand at first glance. Have vague idea what procedures and typecasts are, not a clue how arrays are used.

 

[MrCBofBCinTX]

Okay, after looking at some other posts, found page on regex functions.
I am guessing that I need to use something like


Some examples:

regexp_replace('foobarbaz', 'b..', 'X')
fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
fooXarYXazY



Except replacing 'Millstead' with nothing?

 

[MrCBofBCinTX]

OK, I've got it for making one change at a time.

vendor_prods=# update products set product_description = regexp_replace((select product_description from products where product_description like 'Millstead%' AND product_id=19
), 'Millstead - ', '') where product_id=19;

Works well, Is this the best way to write this?

Now where do I look to figure out how to automate this for a series of records? (this would now be very easy for me to automate with dbi, but I'd like to learn how to do through psql)