Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, 2048. I am using PostgreSQL. How do I do this ? I am fairly new to databases. Any help/suggestion is appreciated.

share|improve this question
add comment

1 Answer

If your data is consistent, so that after a number it always contains a space and then something else, then the following expression will get you the number part:

split_part(original_size, ' ', 1) 

This will split up your string along the spaces with the remaining chunks are indexed from 1 up to the last one, then the first one is picked. There are, of course, other possibilities around, for example using a regex, which probably other will add later :)

Now you can construct your UPDATE statement:

UPDATE size
   SET processed_size = split_part(original_size, ' ', 1);

Notice that there is no WHERE clause as you want to set the second column for every row in the table.

You find an example on SQLFiddle.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.