Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>Try this</p> <pre><code>Declare @str varchar(max) = 'this is misc andrew@g.com and a medium text returning John@acme.com' ;With Cte AS( SELECT Items = Split.a.value('.', 'VARCHAR(100)') FROM ( SELECT CAST('&lt;X&gt;' + REPLACE(@str, ' ' , '&lt;/X&gt;&lt;X&gt;') + '&lt;/X&gt;' AS XML) AS Splitdata ) X CROSS APPLY Splitdata.nodes('/X') Split(a) ) SELECT Email = STUFF(( SELECT ';'+ Items FROM Cte Where Items LIKE '[A-Z0-9]%[@][A-Z]%[.][A-Z]%' FOR XML PATH('')),1,1,'') </code></pre> <p><em>Result</em></p> <p><strong>EmailAddress</strong></p> <pre><code>andrew@g.com;John@acme.com </code></pre> <p>N.B.~ You may need to do the following </p> <p>a) As per your requirement , you need to make a TVF (Table valued function). You can refer the article on <a href="http://www.dotnetfunda.com/codes/code1797-split-function-in-sql-server-using-set-base-approach.aspx" rel="nofollow">Split Function in Sql Server using Set base approach</a></p> <p>b) The Email validation like clause is OK to work with but for more complex requirement you may have to enhance it.</p> <p>c)If needed, you may have to clean the data before applying the filtration clause. e.g. %John@acme.com is an invalid email.so remove the "%" sign and then apply the filtering clause.</p> <p>But as some one mentioned that, it is better not to do the string splitting/ manipulation much in Sql Server side and I agree to his point , so here is a C# code for achieving the same</p> <pre><code>static void Main(string[] args) { string str = "this is misc andrew@g.com and a medium text returning John@acme.com"; var result = GetValidEmails(str).Aggregate((a,b)=&gt;a+";" + b); Console.WriteLine(result); Console.ReadKey(); } private static List&lt;string&gt; GetValidEmails(string input) { List&lt;string&gt; lstValidEmails = new List&lt;string&gt;(); string regexPattern = @"^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$"; foreach (string email in input.Split(' ')) { if (new Regex(regexPattern, RegexOptions.IgnoreCase).IsMatch(email)) { lstValidEmails.Add(email); } } return lstValidEmails; } </code></pre> <p>Hope this will be helpful.</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