strip word off end of field and convert

[Ofina]

I have a field that will sometimes say "20 bundles" or "1 bundle" or "56 bundles".
I would like a formula I can use that will strip the word off the end and then add the bundle numbers together. The trouble I see is that it will sometimes be "bundles" and sometimes be "bundle".

I wonder if I were to do

CAST(RTRIM(LEFT(YourColumnName, LEN(YourColumnName) - 7)) AS integer)

would that do it, do you think?

 

[bborissov]

CAST(LEFT(YourColumnName, CHARINDEX(' ',YourColumnName+' ')-1) AS int)

Not tested!

Borislav Borissov
VFP9 SP2, SQL Server