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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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:

   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

Your Answer


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.