query help

[Guest]

I need to extract data from the left side of a string where the seperator is
a carat ^ . I have looked into the help for the Mid function, but can't seem
to get it work properly. Suggestions please...

 

[Guest]

To get the string on the left to the ^
?left(FieldName,instr(FieldName,"^")-1)

To get the string on the right to the ^
?mid(FieldName,instr(FieldName,"^")+1)

 

[Guest]

I got it. The field name had a space in it so I enclosed it with brackets.
Thanks!

 

[Patty]

Brian,

can you help me with the mid functiuon.

I have a text here: "Predefinedurchase OrderURCH_1.0:43403-1:136"
I want to create a view on the SQL server to pull out only "43403" which is
the PO#. it started right after the _1.0: and it is always a 5 digit numbers.

Thanks,