How do I retrieve only the domain name in SQLite?

Question

My table has got a column with strings like this :

"http://news.yahoo.com/whats-super-tuesday-419-gop-delegates-130610354.html"
"http://www.nytimes.com/2012/03/05/us/politics/republican-party-moves-toward-romney-ahead-of-super-tuesday.html?_r=1&hp"
"http://www.washingtonpost.com/2010/07/08/AB582vC_page.html"

I would like to to get this column :

"http://news.yahoo.com/"
"http://www.nytimes.com/"
"http://www.washingtonpost.com/"

How do I get there?

http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query — sha, Mar 05 '12 at 13:53
Welcome to Stack Overflow. Please read [How to Ask](http://stackoverflow.com/questions/how-to-ask), [What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/), and [How To Ask Questions The Smart Way](http://catb.org/esr/faqs/smart-questions.html). — , Mar 05 '12 at 14:00
Sorry but I don't find the answer in the post you point to : I don't want to find a query string but to replace it — Matthieu, Mar 05 '12 at 14:16

score 1 · Accepted Answer · answered Mar 05 '12 at 18:44

This is C# code and uses .Net regular expressions, but you can tweak as necessary if your regex flavor is a little different:

using System.Text.RegularExpressions;

Regex rx = new Regex( "\"(http://.*?/)(.*?)\"", RegexOptions.IgnoreCase );
string rxReplace = "\"$1\"";

string input = "http://foo.com/fubar.html";

// Produces "http://foo.com/"
string output = rx.Replace( input, rxReplace );

How do I retrieve only the domain name in SQLite?

1 Answers1