Remove a character string using SQL

[brharrii]

I have a weight field that I've been populating with the weight and the unit of measure. I want to seperate those into 2 different fields. I've created a new field called UnitofMeasure and now I need to go back and remove all the instances of 'oz' and 'lbs' from the first field.

Is this possible with SQL?

what is the syntax?

Thank you

 

[jdraw]

Show some sample data.
Also the design of the table can you show the old field and its contents, and the new fields - how they are defined.

We don't know any formatting.

eg 195 lbs
195lbs

 

[brharrii]

This is a sample of the data. there are over 700 records that need to be updated. The data isn't formatted in a perfectly consistent manner unfortunately. the logic I conjoured up in my mind was either to remove all instances of the string 'oz' and all instances of the string 'lbs' from the data. if that is not possible, then the majority of the records would be corrected by assuming that each cell has a numerical value and anything after that numerical value should be stripped.


Thanks for your help.

 

[jdraw]

Are you planning on changing the datatype as well?

If you have current field 9.0oz,
you can

1 - check for the string "oz", then update your new field to "oz" or an identifier (eg 1 oz, 2 lbs, 3 Ton,....)
2 - you could replace "oz" with Nulls, or you could move the move the numeric data (9.0) to a new field datatype Single/Double.

It can be done with a few sql statements in my view, but you will have to decide what to do with the 9.0 in the current text field.

 

[brharrii]

After I clean everything up, I do plan on changing the data type yes.

The new field was pretty easy to add and update fortunately. I was able to use a query based on a different description field to determine if the unit of measure should be oz, lbs, etc. That part is all done.

Right now I just need the units of measure stripped from the original fields so that all that is remaining is numerical data. The only units of measure of consequence for right now are oz and lbs.

Thanks again

 

[MSAccessRookie]

This is a sample of the data. there are over 700 records that need to be updated. The data isn't formatted in a perfectly consistent manner unfortunately. the logic I conjoured up in my mind was either to remove all instances of the string 'oz' and all instances of the string 'lbs' from the data. if that is not possible, then the majority of the records would be corrected by assuming that each cell has a numerical value and anything after that numerical value should be stripped.


Thanks for your help.

I believe that a combination of the Replace() and the Trim() Functions will give you what you want. Look them up and see if you can get them to work.

• Choose a Search Tag and Replace() it with ""
• Trim() the result to remove any leading/trailing spaces
• Repeat until all Tags have been removed.

-- Rookie