Stripping specific text strings using SQL

[brharrii]

I am currently using a field to store weight values. It holds both the numerical weight as well as 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

[June7]

Will every record have a value (no nulls or empty strings)?

Need to know structure of the stored values.

Returning just the number part could be simply: Val([fieldname])

Extracting the unit is a little more complicated. Is there always a space between the weight and the unit?

Mid([fieldname], InStr([fieldname, " ") + 1)

[brharrii]

Thanks for your reply June. This is essentially what my data looks like. Unfortunately it isn't in a perfectly uniform format. The val([fieldname]) thing sounds like it might do what I'm trying to accomplish though. How would I integrate that into a query?

Thank you

[rpeare]

june raises some good questions specifically relating to HOW the data is stored.

For instance can you have:

7oz
7 oz
7 ounce
7 ounces
7ounce
7ounces
oz7
oz 7
ounce 7
ounces 7
ounce7
ounces7

all the same value, but all different text values

If this is the case you're likely going to have to run some VB code to parse the field correctly. If your number always starts in the first position it becomes easier but still would require code.

[June7]

Yes, inconsistencey of data definitely complicates and a VBA function to handle all possible configurations is the only recourse.

If the number is always at beginning, a SELECT query would be like:

SELECT *, Val([PortionMinWeight]) AS PMW FROM tablename;

The Val expression can be used in an UPDATE sql as the UPDATE TO value.

If data is always in the form of number first then unit, make sure all values have a space. For a query to locate records, try:

SELECT * FROM tablename WHERE PortionMinWeight Not Like "* *" OR PortionMaxWeight Not Like "* *";

Then manually fix those values. Hopefully not too many.

Then use the expression to extract unit and update new field.

[brharrii]

Am I correct in understanding that there isn't a way to search for a specific string i.e. "oz" and delete it?

The biggest portion of my data is in the format [# "unit"]. Would it be possible to create a query that addressed this specific format and I can manually clean up the few that remain after?

[rpeare]

Hah you posted too quickly!

Yeah you're going to need code to split that out.

Let's also say your table is called 'portions'.

Create the field PORTIONMINWEIGHT_LABEL in your table.
Create the field PORTIONMINWEIGHT_CONV in your table.

then you'd want to run some code something like:

Dim db As Database
Dim rst As Recordset
Dim sCurrString As String
Dim iStringLen As Integer
Dim sTestString As String


Set db = CurrentDb
Set rst = db.OpenRecordset("Select * FROM portions")
rst.MoveFirst

Do While rst.EOF <> True
    sCurrString = rst.Fields("portionminweight")
    iStringLen = Len(sCurrString)
    i = 1
    
    If IsNumeric(sCurrString) = False Then
        Do While i <= iStringLen
            If IsNumeric(Left(sCurrString, i)) = True Then
                i = i + 1
            Else
                i = i - 1
                rst.Edit
                rst.Fields("PortionMinWeight_Conv").Value = Trim(Left(sCurrString, i))
                rst.Fields("PortionMinWeight_Label").Value = Trim(Right(sCurrString, Len(sCurrString) - i))
                rst.Update
                GoTo EXITLOOP
            End If
        Loop
    Else
        rst.Edit
        rst.Fields("PortionMinWeight_Conv") = sCurrString
        rst.Update
    End If
EXITLOOP:

    rst.MoveNext
Loop
rst.Close
Set db = Nothing

when you're done the two new columns should be populated then you can delete your current portionminweight field and rename your portionminweight_conv to your original field name.

[June7]

Yes, I think a query would be possible or you can go with rpeare's code.

[brharrii]

Yes, I think a query would be possible or you can go with rpeare's code.

I'm not too good with VBA, I'll have to play with it to see if I can figure it out. If there is a way to do this in SQL and you wouldn't mind giving me an example, I'd feel more comfortable with that


Thank you both for your assistance

[June7]

I already showed one example to select records that don't have a space in the value.

If you want to find all records that don't begin with a number as well as lacking the space, try:

SELECT * FROM tablename WHERE Not IsNumeric(Left(PortionMinWeight,1)) OR Not (IsNumeric(Left(PortionMaxWeight,1)) OR PortionMinWeight Not Like "* *" OR PortionMaxWeight Not Like "* *";

[rpeare]

You shouldn't really need to alter my code at all other than to put in your real table name and real field names. Just remember to create 2 new fields for every field you're looking to split so you can verify the results before making them permanent.

[brharrii]

thanks Guys, I appreciate the help. I'll play with these suggestions