Search code examples
sqlsql-serverdatabasesql-like

SQL LIKE Statment that applies on everything up to a certain character

My database contains a column for "website" with the following data:

foo1.web.com
foo2.gov
doo3.shoo.net
baa.com
baa2.shoo.com

I am looking to do a select statement that grabs everything like the variable but only up to the first period, I want to ignore everything after the period:

DELCARE @variable varchar(MAX);
SET @variable = 'oo';

SELECT * WHERE website LIKE '%' + @variable + '%' --(but only apply like statement up to the first .)

So what I would get back would be:

foo1.web.com
foo2.gov
doo3.shoo.net

but it would leave out 

baa2.shoo.com

Thanks for the help in advance!

EDIT: Using SQL Server

Solution

  • For SQL Server, you can look at only the left N characters of your URL for oo. You would get the left N characters by using LEFT and CHARINDEX to find the first .

    SELECT * 
FROM @table
WHERE LEFT(val, CHARINDEX('.', val)) LIKE '%'+@variable+'%'