StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is a failure of your model. Instead of storing the locations as a delimited string it's probably a good idea to make a 1-n table to store the locations in instead. And in fact the correct 'answer' to your question is probably "redesign this part of the database!"</p> <p>However, to do what you want you can do stuff like this:</p> <pre><code>USE tempdb GO /* udfSplit (A Fast String Splitter) ************************************************************** * * Uses a number table to *very* quickly split the text (@text). Splits on the delimiter (@d) * Returns Table of ( [RowID], [SplitText] ). Inlineable for CROSS APPLY etc. * * Charlie * *************************************************************************************************/ CREATE FUNCTION [dbo].[udfSplit] (@text NVARCHAR(4000), @d NVARCHAR(50)) RETURNS TABLE AS RETURN ( WITH numbers(n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY a.[n]) FROM ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS a ([n]) CROSS JOIN ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS b ([n]) CROSS JOIN ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS c ([n]) CROSS JOIN ( VALUES (0), (1), (2), (3), (4)) AS d ([n]) ) SELECT [RowID] = ROW_NUMBER() OVER ( ORDER BY [n] ASC ) , [SplitText] = SUBSTRING( @d + @text + @d , [n] + LEN(@d) , CHARINDEX(@d, @d + @text + @d, [n] + LEN(@d)) - [n] - LEN(@d) ) FROM numbers AS n WHERE [n] <= LEN(@d + @text + @d) - LEN(@d) AND SUBSTRING(@d + @text + @d, [n], LEN(@d)) = @d ) GO IF OBJECT_ID('tempdb..#sample') IS NOT NULL DROP TABLE #sample GO CREATE TABLE #sample ( name VARCHAR(255) , locations VARCHAR(MAX) ) INSERT #sample (name, locations) VALUES ('a', 'ab-cd') , ('b', 'ab-cd-ef') , ('c', 'gh') , ('d', NULL) ; WITH SPLIT AS ( SELECT [name], l.* FROM #sample AS s OUTER APPLY dbo.[udfSplit](s.locations,'-') AS l ) SELECT s.name , MAX(CASE WHEN s.rowId = 1 THEN s.SplitText ELSE '' END) AS a , MAX(CASE WHEN s.rowId = 2 THEN s.SplitText ELSE '' END) AS b , MAX(CASE WHEN s.rowId = 3 THEN s.SplitText ELSE '' END) AS c , MAX(CASE WHEN s.rowId = 4 THEN s.SplitText ELSE '' END) AS d FROM SPLIT AS s GROUP BY s.name </code></pre> <p>This probably looks super complicated. The function udfSplit is a very fast string splitter -- it turns your delimited string into a table returning postion (1-4) and the split string. Unless you really want to get into it just don't worry how it works. If you <em>do</em> want to learn about splitting strings in the db (and in general why that's a bad plan) -- then read here:</p> <p><a href="http://www.sqlservercentral.com/articles/Tally+Table/72993/" rel="nofollow">http://www.sqlservercentral.com/articles/Tally+Table/72993/</a></p> <p>The rest of the code makes up a sample table and then does a select on it to get the output you wanted:</p> <pre><code>(4 row(s) affected) name a b c d -------------------- ----- ----- ----- ----- a ab cd b ab cd ef c gh d </code></pre> <p>The MAX(CASE....) Expressions are a pivoting trick back in sql server 2000 land. I never got the hang of the PIVOT operator.</p> <p>SQL Fiddle: <a href="http://sqlfiddle.com/#!3/80f74/1" rel="nofollow">http://sqlfiddle.com/#!3/80f74/1</a></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