Given a string in a SQL Server text column:
AAAAAA^BBBBB^CCCC^^^^
I am trying to strip each section into its own column.
I have tried taking a shot at doing this myself, but am unable to figure out how to get the last value 'CCCC'.
Is there an easier approach to doing this?
Here is my SQL so far:
SELECT
SUBSTRING('AAAAAA^BBBBB^CCCC^^^^',0,CHARINDEX('^','AAAAAA^BBBBB^CCCC^^^^')) as A,
SUBSTRING('AAAAAA^BBBBB^CCCC^^^^',CHARINDEX('^','AAAAAA^BBBBB^CCCC^^^^')+1, CHARINDEX('^','AAAAAA^BBBBB^CCCC^^^^',CHARINDEX('^','AAAAAA^BBBBB^CCCC^^^^')+1) -CHARINDEX('^','AAAAAA^BBBBB^CCCC^^^^')-1) as B
Which results in the following:
| A | B |
|---|---|
| AAAAAA | BBBBB |
How can I extract the CCC value to its own column?
