StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Use <a href="http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx" rel="nofollow">this string splitting function</a> to produce a table of results.</p> <p>I would first call <code>dbo.split()</code> using a <code>,</code> as the separator character. Then you'll have a list of items like:</p> <pre><code>Nissan:blue:20000 Ford:green:10000 Nissan:steel:20001 Ford:blue:10001 Chevy:blue:10000 Ford:olive:10000 </code></pre> <p>From there you can call <code>dbo.split()</code> again using <code>:</code> as your separator. Each call will result in exactly three records (assuming your design as at least that "normal").</p> <p>As @JNK mentioned in his comment, hopefully this is not something you'd want to be running regularly.</p> <p><strong>EDIT:</strong></p> <p>Some sample code to get you started:</p> <pre><code>SELECT * INTO #YuckyCar FROM ( SELECT 1 ID, 'Nissan:blue:20000,Ford:green:10000' CarData UNION SELECT 2, 'Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000' ) T; -- Shows logical step #1 SELECT ID, X.items MoreCarData FROM #YuckyCar CROSS APPLY dbo.Split(CarData, ',') X; -- Shows logical step #2 SELECT Q.ID, Y.items FROM ( SELECT ID, X.items MoreCarData FROM #YuckyCar CROSS APPLY dbo.Split(CarData, ',') X) Q CROSS APPLY dbo.Split(Q.MoreCarData, ':') Y DROP TABLE #YuckyCar; </code></pre> <p>The problem in the last part is that you can't guarantee row 1 = Manufacturer, row 2 = Color, row 3 = Cost.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload