I have this code:
SELECT PARSENAME(REPLACE('Academic: Teacher, Foreign Language - [Spanish], 5-12, Emergency', ',', '.'), 3)
But it returns a null. If I remove the [ and ], it works as desired and returns Foreign Language - Spanish.
My question is how do I can make this work?
5
You could replace the [] square-brackets with other special characters that will not be present in the data, and then replace the special characters back with the correct brace.
This example just uses curly braces:
SELECT REPLACE(REPLACE(PARSENAME(REPLACE(REPLACE(REPLACE('Academic: Teacher, Foreign Language - [Spanish], 5-12, Emergency', ',', '.'), '[', '{'),']', '}'), 3),'{', '['), '}', ']')
But frankly, this is an abuse of the ParseName() function. I certainly wouldn’t want to be the one to maintain this. You will be better off doing this work in the client code or reporting tool.