Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Change existing values

Status
Not open for further replies.

MrCBofBCinTX

Technical User
Dec 24, 2003
164
0
0
US
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:

Code:
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)

Code:
Millstead - 2
and
Code:
 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
Code:
2 In. x 6 In. x 12 Ft.
as end result.
 
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.
 
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?



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

Code:
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top