sql column value splitting

Question

I have an sql column which consists of four concatenated words separated with slashes ("/"). The words can be any random words of variable length. How can I retrieve each word separately. What is the query I need to write?

Thanks

First: Bad table design! Second: See [this answer](http://stackoverflow.com/questions/5829543/how-to-parse-a-string-and-create-several-columns-from-it/5829640#5829640) to get the idea, just replace `'_'` with `'/'` — Ocaso Protal, May 28 '11 at 12:08
That is only helpful if there is only one slash, but i have three slashes. — RAHUL, May 28 '11 at 12:12
C'mon! Than you have to do this three times with some additional `SUBSTRING` magic — Ocaso Protal, May 28 '11 at 12:14
but i cannot distinguish between the slashes....can u please provide the whole query? — RAHUL, May 28 '11 at 12:16

score 0 · Answer 1 · answered May 28 '11 at 12:20

Not "the fastest way" but it will work.

with extract1 as 
(
   SELECT substring(data, 1, charindex('/', data)-1) AS data1, 
  substring(data, charindex('/', data)+1, LEN(data)) AS data
  from atable
), extract2
(
   SELECT data1,substring(data, 1, charindex('/', data)-1) AS data2, 
  substring(data, charindex('/', data)+1, LEN(data)) AS data
  from extract1
)
  SELECT data1,data2,substring(data, 1, charindex('/', data)-1) AS data3, 
  substring(data, charindex('/', data)+1, LEN(data)) AS data4
  from extract2

sql column value splitting

1 Answers1