Search code examples
sqldb2sql-like

SQL Parameter Compare to Whitelist Column Values

I have a table that has a whitelist of all valid domains. The column in question is called "URL"

URL column is VARCHAR(60) and @url is a .Net string primitive.

I want a SQL statement that returns "1" if the SQL parameter provided starts with the whitelisted URLs I have stored on my table. This is what I have:

SELECT  1
  FROM  [TABLE]
 WHERE  @url LIKE (URL || '%')

But it doesn't work and gives the following error:

A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string.

An example of what I'd like to happen is when given the parameter value of HTTP://WWW.GOOGLE.COM/ANYTHING/AFTER/THIS and there is a row on my table that looks like HTTP://WWW.GOOGLE.COM it will return "1", but if there is not a row that starts with the domain then return null.

Example table values:

HTTP://WWW.GOOGLE.COM
HTTPS://WWW.ANOTHERWEBSITE.GOV
HTTP://WWW.DOMAIN.CORP
HTTP://MY.WEBSITE.COM
HTTPS://STUFF/SUBDOMAIN/
HTTP://BUSINESS.JUNK.CORP/
Solution

  • How about:

    SELECT 1
FROM table
WHERE LEFT(URL, LENGTH(@url)) = @url;

    or

    SELECT 1
FROM table
WHERE LOCATE(URL, @url,1) = 1;

    EDIT:

    Depending of your datatypes I suggest to use cast:

    CAST(URL AS VARCHAR(100))  -- instead of URL
CAST(@url AS VARCHAR(100)) -- instead of @url

    EDIT FROM POSTER:

    The combination of the above solutions solved my problem, I'll post the SQL that I used below:

    SELECT  1
  FROM  table
 WHERE  LOCATE(URL, CAST(@url AS VARCHAR(60)), 1) = 1