|
|
Posted by markc600 on 05/13/07 08:59
Using a table of numbers
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
CREATE TABLE MyTable(ID VARCHAR(4), Data VARCHAR(100))
INSERT INTO MyTable(ID, Data) VALUES('ID1','12x100,56x12,90x90')
SELECT ID,
PARSENAME(REPLACE(Data,'x','.'),2) AS First,
PARSENAME(REPLACE(Data,'x','.'),1) AS Second
FROM (
SELECT ID,
SUBSTRING(Data ,
Number,
CHARINDEX(',' ,
Data+',' ,
Number)-Number) AS Data
FROM MyTable
INNER JOIN Numbers ON Number BETWEEN 1 AND LEN(Data)+1
AND SUBSTRING(','+Data,Number,1) = ',') X
ORDER BY ID
Navigation:
[Reply to this message]
|