Post reply

easy wildcard question

  • November 9, 2014 at 9:17 am

    #292367

    Hi,

    I have column in a table (tblDomains) called "domain".

    I am building dynamic SQL (don't shout at me) where I pass in a URL.

    I then need to find rows where that domain is included in the URL.

    This is what I tried so far...

    select * from tblDomains where (domain + '%') like 'mywebsite.co.uk/folder/page.html'

    which returns nothing. There is a row where domain contains 'mywebsite.co.uk' which I would like to have returned.

    You can see that I've tried to add a wildcard to the value in the domain column, but it is treated as a very literal percent and not a wildcard.

    Am I missing something very obvious?

    It's very typical to search for a string within a value in the db, but not so obvious how to do the inverse of this.

    Thanks in advance

    Tim

  • November 9, 2014 at 9:47 am

    #1757337

    Quick suggestion, use the charindex or patindex functions

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @URL VARCHAR(150) = 'mywebsite.co.uk/folder/page.html';

    DECLARE @tblDomain TABLE

    (

    Domain_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

    ,Domain VARCHAR(100) NOT NULL

    );

    INSERT INTO @tblDomain(Domain) VALUES

    ('yourwebsite.co.uk')

    ,('mywebsite.co.uk')

    ,('hiswebsite.co.uk')

    ,('herwebsite.co.uk')

    ,('othersite.com')

    ,('yetanother.net');

    SELECT

    D.Domain_ID

    ,D.Domain

    FROM @tblDomain D

    WHERE CHARINDEX(D.Domain,@URL,1) > 0;

    Results

    Domain_ID Domain

    ----------- -----------------

    2 mywebsite.co.uk

  • November 9, 2014 at 9:53 am

    #1757338

    Thanks for the reply. I actually found a really quick solution to the problem that I'm not sure is documented anywhere.

    I was trying this

    select * from tblDomains where (domain + '%') like 'mywebsite.co.uk/folder/page.html'

    which I changed to this

    select * from tblDomains where 'mywebsite.co.uk/folder/page.html' like (domain + '%')

    It's a simple switch of the 2 sides of the "like" to get the wildcard on the right, not the left. Wildcards on the left of a like statement don't work, but on the right they do!

    I knew it would be something simple (although not very obvious)

  • November 9, 2014 at 10:06 am

    #1757339

    tswalton (11/9/2014)

    Thanks for the reply. I actually found a really quick solution to the problem that I'm not sure is documented anywhere.

    I was trying this

    select * from tblDomains where (domain + '%') like 'mywebsite.co.uk/folder/page.html'

    which I changed to this

    select * from tblDomains where 'mywebsite.co.uk/folder/page.html' like (domain + '%')

    It's a simple switch of the 2 sides of the "like" to get the wildcard on the right, not the left. Wildcards on the left of a like statement don't work, but on the right they do!

    I knew it would be something simple (although not very obvious)

    Have a look at this: LIKE (Transact-SQL)

    😎

  • November 9, 2014 at 10:17 am

    #1757340

    Out of curiosity, why are you using dynamic sql, looking at this query, I cannot see the need for it?

    😎

  • November 9, 2014 at 11:50 am

    #1757343

    I'm editing an existing application. The dynamic SQL exists already, I just needed to tweak it a bit.

  • November 9, 2014 at 12:07 pm

    #1757344

    tswalton (11/9/2014)

    I'm editing an existing application. The dynamic SQL exists already, I just needed to tweak it a bit.

    Guessed it would be something along those lines, just hope you are dealing with a response log, not a request log;-)

    😎

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply