table1
value
100 x 200 x 300 x 400 `I want to split this row bcoz more than 2 values`
100 x 200
1300 x 1400
200 x 300 x 1200 x 2200 `I want to split this row bcoz more than 2 values`
....
Value column is not fixed length, it may also contain 10 x 200 x 3000 x 5
I want to take the first 2 values then the next 2 values will go to the next row after x
I want to split the rows like this:
Expected Output
100 x 200
300 x 400
100 x 200
1300 x 1400
200 x 300
1200 x 2200
....
Hint:
We can split the value for each x then we can join 2 values
for example 20 x 10 x 5 x 1
Split like this 20, 10 5, 1 then join 20 x 10, 5 x 1
The above method will work out, if it work out means then can i get some query for splitting and joining the rows..
How can I do this in SQL?
Need SQL Query Help
SELECT Rtrim(Substring(value, 1, CHARINDEX( 'x' , value, CHARINDEX( 'x', value) + 1) - 2))
FROM (SELECT value
FROM table1
Where LEN(value) - LEN(REPLACE(value, 'x', '')) >= 2) as temp
UNION ALL
SELECT Substring(value, CHARINDEX( 'x' , value, CHARINDEX( 'x', value) + 1) + 2, LEN(value))
FROM (SELECT value
FROM table1
Where LEN(value) - LEN(REPLACE(value, 'x', '')) >= 2) as temp1