StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, create a simple string splitting function:</p> <pre><code>CREATE FUNCTION [dbo].[SplitString] ( @List NVARCHAR(MAX), @Delim VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT [Value] FROM ( SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number], CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number]))) FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name) FROM sys.all_objects) AS x WHERE Number <= LEN(@List) AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim ) AS y ); </code></pre> <p>Then, you can use it in an <code>OUTER APPLY</code>:</p> <pre><code>declare @T table (id int, fname varchar(50), tname varchar(50), email varchar(200)) insert into @T values (1, 'fname1', 'tname1', 'xxxxxx@msn.com xxx.xxxxx.com') insert into @T values (2, 'fname2', 'tname2', 'just a bunch of usless data') insert into @T values (3, 'fname3', 'tname3', 'xxx@xx.xx.com') insert into @T values (4, 'fname4', 'tname4', 'xxx@xx.xx.com xxx@yahoo.com') insert into @T values (5, 'fname5', 'tname5', 'xxx@gmail.com xxxx.msn.com') insert into @T values (3, 'fname6', 'tname6', 'xxx@msn.com') SELECT t.id, t.fname, t.tname, f.Value FROM @T AS t OUTER APPLY dbo.SplitString(REPLACE(t.email, ' ', ';'), ';') AS f WHERE t.email LIKE '%@%' AND f.Value LIKE '%@%'; </code></pre> <p>Results:</p> <pre class="lang-none prettyprint-override"><code>id fname tname Value ---- ------ ------ -------------- 1 fname1 tname1 xxxxxx@msn.com 3 fname3 tname3 xxx@xx.xx.com 4 fname4 tname4 xxx@xx.xx.com 4 fname4 tname4 xxx@yahoo.com 5 fname5 tname5 xxx@gmail.com 3 fname6 tname6 xxx@msn.com </code></pre> <p>More on split functions (and better alternatives, if splitting strings coming from the application layer):</p> <p><a href="http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings" rel="nofollow">http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings</a></p> <p><a href="http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql" rel="nofollow">http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql</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