-1

I have a field containing 4 different informations, separated by as dash ("-"). The code to separate it with CHARINDEX, RIGHT, LEFT, SUBSTRING, can get very awkward. Is there a more readable solution?

Sample data:

AR - RE - Dir. Asuntos Publicos y RSE - Asuntos Publicos

Desired output:

AR
RE
Dir. Asuntos Publicos y RSE
Asuntos Publicos

Code I've come up so far:

RIGHT(RIGHT(T.TASK_RESUME,LEN(T.TASK_RESUME)-CHARINDEX(' - ',T.TASK_RESUME,1)-2),LEN(RIGHT(T.TASK_RESUME,LEN(T.TASK_RESUME)-CHARINDEX(' - ',T.TASK_RESUME,1)-2))-CHARINDEX(' - ',RIGHT(T.TASK_RESUME,LEN(T.TASK_RESUME)-CHARINDEX(' - ',T.TASK_RESUME,1)-2),1)-2
Rafael
  • 91
  • 1
  • 1
  • 9
  • If you have a table full of data in the exact format and you need to frequently access individual components, split the string when you *insert* data into 4 additional columns then its not an issue. Failing that look at *t-sql split* questions for many alternatives. – Alex K. Jan 29 '15 at 14:08
  • I don't see the reason why you voted down my question... I work with Business Intelligence, and in many many times I need to consume the database as it is, I can't just change the application that inserts the data. And SQL can get very very ugly to do some basic splittings like this. I think it's a fair question to be asked here... – Rafael Jan 30 '15 at 19:18

1 Answers1

0

I don't know if this is good way but can be like this too:

DECLARE @row VARCHAR(200) = 'AR - RE - Dir. Asuntos Publicos y RSE - Asuntos Publicos'

SET @row = '<row>' + REPLACE(@row, ' - ', '</row><row>') + '</row>'

DECLARE @xml XML = CAST(@row AS  xml)

SELECT r.v.value('text()[1]','varchar(max)')
FROM @xml.nodes('//row') as r(v)

Result:

AR
RE
Dir. Asuntos Publicos y RSE
Asuntos Publicos
Darka
  • 2,762
  • 1
  • 14
  • 31