MS-SQL Server query to find record where column row is part of nominated string using Classic ASP

Question

The query needs to find a record where the value stored in a column row matches the nominated variable. For example the column row value might be https://example.com/docs/ and the nominated url might be https://example.com/docs/some.html.

By using a query like this:

SQL = "SELECT * FROM Documents Where Link LIKE '" & strWebPage & "%' "

If

Link = "https://example.com/docs/" 
strWebPage = "https://example.com/docs/"

then a match is found. Likewise if

Link = "https://example.com/docs/some.html" 
strWebpage = "https://example.com/docs/"

a match is found as well.

But if

Link = "https://example.com/docs/" 
strWebPage = "https://example.com/docs/some.html"

then no match is found.

In other words we can find a match if Link and strWebPage are identical or strWebPage is part of Link. But we cannot find records where the Link value is part of strWebPage.

Is there a solution to this problem by using a simple query, meaning without installing full-text search?

WilliamK · Accepted Answer · 2023-07-09 00:40:54Z

0

You can do this by performing an INSTR check as a loop within the query.

SQL= "SELECT Link FROM Documents "
Set rsSQL = myConnection.Execute(SQL)
if NOT rsSQL.EOF then
    Do While strFound <> "1" and NOT rsSQL.EOF
        strID = rsSQL("ID").value
        strLink = rsSQL("Link").value                               
        if Instr(strWebpage, strLink) <> 0 then
            strFound = "1"
        end if
    rsSQL.MoveNext
    Loop                                
end if
rsSQL.Close
Set rsSQL = Nothing

Then when you have the Documents ID you can get its data and move forward...

If strFound = "1" then                          
    SQL = "SELECT * FROM Documents Where ID = '" & strID & "' "
    Set rsSQL = myConnection.Execute(SQL)   
    if NOT rsSQL.EOF then
        strA = rsSQL("A").value
        strB = rsSQL("B").value     
        strEtc = rsSQL("Etc").value                         
    end if
    rsSQL.Close
    Set rsSQL = Nothing
else
    do other stuff
end if

answered Jul 9, 2023 at 0:40

WilliamK

6922 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user16674741 Over a year ago

I don't recall if INSTR, which was available for Access DB queries but not for SQL queries, ever worked both ways, but using it like this example has solved my problem, thanks.

Collectives™ on Stack Overflow

MS-SQL Server query to find record where column row is part of nominated string using Classic ASP

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related