Split Up Text In A Column

Question

I have this piece of text that is stored in our MS-SQL database (ignore the quotes and no, I can't redesign how this work specifically):

"TEST|00000298398293|EQ5|Patient"

Now, when I do a simple select, I get that result being returned. What I'd like to do is split that string based on the "|" character and return the individual strings associated with this string, so that I could have "TEST", "0000298398293", "EQ5" and "Patient" in different fields. How can I do this? In PHP, you can use the explode method, is there something like that in MS-SQL?

@bluefeet i don't think so, bardoo is asking to split text of a column... but not into different rows! — ivoputzer, Apr 30 '13 at 19:21
This is pretty ugly; databases were designed for structured data and you're losing a lot of their advantages by storing data in this format. I agree with Ivo, you might want to consider a redesign if you have that luxury. — eriknelson, Apr 30 '13 at 19:23

score 1 · Accepted Answer · answered Apr 30 '13 at 19:18

It's surely not the most elegant solution but i've used in in the past:

DECLARE @Sql varchar(50) = 'TEST|00000298398293|EQ5|Patient'


SELECT
    PARSENAME(REPLACE(@sql,'|','.'),4),
    PARSENAME(REPLACE(@sql,'|','.'),3),
    PARSENAME(REPLACE(@sql,'|','.'),2),
    PARSENAME(REPLACE(@sql,'|','.'),1)

Notice : This only works if you have 3 pipes, eventually consider to redesign your database in the future!

Split Up Text In A Column

1 Answers1