SELECT * FROM tbPROCESSED
This is the full picture of what I'm trying to do.The problematic field is called Actual_Text which is the string that requires manipulation.
I'm deriving three fields from field Actual_Text
fields Type,Code & MonetoryValue
Field Type is the digits starting from the left and end when it encounters a space
only where there is a Valid monetoryValue
ie. '80 98437 3 997 -2.500'
in this case Type will be = 80
ie. 'PARK AVENUE MANOR Pr RAZOO 0'
in this case Type will be = null
ie. '530719000254'
in this case Type will be = null
-----------------------------Code
Field Code is the digits after the first space starting from the left and ends when it encounters the
second space ,only where there is a Valid monetoryValue
ie. '80 98437 3 997 -2.500'
Code will be = 98437
ie. 'PARK AVENUE MANOR Pr RAZOO 0'
Code will be = null
ie. '530719000254'
Code will be = null
ie. '01 6060 3 997 -20.000 CO'
Code will be = 6060
ie. '0997 2247'
Code will be = null because there is no MonetoryValue
---MonetoryValue
I can extract the monetory value only problem is ho do I remove
the alpha characters without using replace
i.e '01 6060 3 997 -20.000 CO'
'-20.000 CO' to exclude CO etc...
'01 6060 3 997 -50.000 CB'