MySql : Break string by delimiters (^A)

Question

I have a string that is of the:

type 'field1^Afield2^Afield3^A'

I need to break this string and get one of the fields. All the string functions I see in MySql that help you break by delimiter expect a string as the delimiter ,

example: SUBSTRING_INDEX("Hello. Break this. for me", ".", 1) would give = "Hello"

How do i break mu string for the escape sequence ^A , it doesn't work if I use "^A" as my delimiter in the SUBSTRING_INDEX function.

Any suggestion will be greatly appreciated.

Answer 1

You can embed control-As directly into the string, at least on the CLI. To type it you need to first type ctrl-V. IE, type the break as double-quote control-v control-a double-quote.

I'm sure there are \ escape codes that could be used too...