Pull out text from field with delimited character

, ,

I am working on a query and have created some calculated fields to store the data but can not find the correct syntax to get the information.

Here is the data in a field called Order.Comment
MORTON HOME CENTER_3726 HIGHWAY 80_MORTON_MS_39117_*GALV

What I want to do is pull out the text in different calculated fields using the _ as the delimiter
So in calculated field 1 I need MORTON HOME CENTER
In calculated field 2 I need 3726 HIGHWAY 80
Incalculated field 3 I need Morton
etc.

I was able to get the firs bit of information with the script below but did not work on the others.

SubString(OrderHed.OrderComment,0, CharIndex(’_’, OrderHed.OrderComment))

calculated field1 = case when [Ice].num_entries(OrderHed.OrderComment,’ _ ') >= 1 then [Ice].entry(1, OrderHed.OrderComment, β€˜_’) else β€˜β€™ end

calculated field 2 == case when [Ice].num_entries(OrderHed.OrderComment,’ _ ') >= 2 then [Ice].entry(2, OrderHed.OrderComment, β€˜_’) else β€˜β€™ end

…etc.

Ross

1 Like

I have this a try but no luck. Getting an error of β€œInvalid column Name β€˜Comment’ β€œ

case when [Ice].num_entries(OrderHed.Comment,’’) >= 2 then [Ice].entry(2, OrderHed.Comment,’’)
else β€˜β€™
end

image001.png

I got it to work. Thanks for pointing me in the right direction.

Case When OrderHed.OTSAddress1 <> β€˜β€™ then OrderHed.OTSAddress1
When OrderHed.OrderComment = β€˜β€™ then β€˜β€™
else [Ice].entry(2, OrderHed.OrderComment , β€˜_’)
end

image001.png

Very good, happy to help.

Ross