[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Wildcard Help

Posted on 2005-04-27
32
Medium Priority
?
265 Views
Last Modified: 2010-03-19
Assuming that URL = http://www.test.com, can you explain any reason why this statement

SELECT *
FROM LINKS
WHERE URL LIKE 'URL%'

Finds http://www.test.com but not http://www.test.com/index.html

???

Thanks,
MT
0
Comment
Question by:movoni
  • 15
  • 6
  • 3
  • +5
32 Comments
 
LVL 12

Expert Comment

by:laotzi2000
ID: 13882631
It should has something to do with how you pass in the value of URL.

It should work like this:
SELECT *
FROM LINKS
WHERE URL LIKE 'http://www.test.com%'
0
 

Author Comment

by:movoni
ID: 13882651
I kinda simplified the code.  URL uses a replace statement in ASP... here's the full code

SELECT *
FROM LINKS
WHERE URL LIKE '12345%'

And then later in the code, 12345 is replaced using:

rsSimilar.Source = Replace(nested_rsSimilar, "12345", rsSearch.Fields.Item("URL").Value)

So, the SQL does end up reading http://www.test.com%
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13882921
I would recommend using SQL profiler to make sure that it is sending the SQL you think it is.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:movoni
ID: 13882943
I'll give it a  shot.  As a side note, when I manually replace rsSearch.Fields.Item("URL").Value with "http://www.test.com" it works fine.  So, I am thinking it has something to do with quote or something.  I tried Trim and that didn't help either.

Thanks,
MT
0
 
LVL 34

Expert Comment

by:arbert
ID: 13882958
This is easy to reproduce.  The / when encountered with the like statement is treated as an ESCAPE code.

Take a look in booksonline for ESCAPE.
0
 

Author Comment

by:movoni
ID: 13883333
Well, "SELECT *  FROM dbo.NWL_LINKS  WHERE URL LIKE '12345%' ESCAPE '/' AND ACTIVE = 1  ORDER BY LinkID, URL" doesn't work.  Any ideas??  Increased to 500 so I can solve this.
0
 
LVL 4

Expert Comment

by:roshkm
ID: 13883437
Is '/' coming in as a key letter?  like '['   or '_' ...  The key letters include ' , _ , [ , -,%.. Are there more?

A simple select will do the trick..

SELECT *
FROM LINKS
WHERE URL LIKE 'http://www.test.com%'  as  laotzi2000  (Date: 04/27/2005 08:54PM PDT) has pointed out..

There might be something else in the value which you are expecting..  You can test with the following select:

SELECT *
FROM LINKS
WHERE URL LIKE 'http:%'    where it should display all the hyper links..

If the expected result is still not coming.. there may be one the above mentioned key letter coming in the picture..

Regards,
RKM
0
 

Author Comment

by:movoni
ID: 13885140
Not sure what you mean by "key."  I can't just type in the URL like I did in my test (that seemed to work) in my 2nd comments after my initial post... it has to come from the database using rsSearch("URL").  The first part of the URL works fine... it will display http://www.test.com.  It will not display http://www.test.com/index.html.  

It's the last '/' and anything after it that's not displaying... it's like it terminates the wildcard.  And, I'm sure there will be cases where there are more than one, but I never know when.  In other words, there will be times when the URL is like http://www.test.com/test/index.html.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13886430
Looks like you have some code issues to me.  I just tested this and received the expected results:

create table #test (
testit varchar(255)
)
go

insert into #test (testit) values ('http://test.com/test.html')
insert into #test (testit) values ('http://test.com/test/hello')
insert into #test (testit) values ('http://test.com/hello.html')
go

select * from #test where testit like 'http://test.com%'
select * from #test where testit like 'http://test.com/hello.html%'

drop table #test
0
 

Author Comment

by:movoni
ID: 13887531
Yes, if I manually type in http://test.com%" it works fine as stated above.  The problem appears to be when 12345 is replaced with the URL from the database, the third slash (after the http://) kills the wildcard in the select statement.  I'll keep messing with it and maybe I'll find a fix or someone here will.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13888105
Nothing in SQL is going to "kill" the wildcard.  Maybe the "%" is some type of special symbol so that it isn't making it thru to SQL.  You might want to try using two %s just to see (it won't hurt anything in SQL even if both appear):

WHERE URL LIKE '12345%%'
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13890680
And capture it in profiler!! I don't think its sending the SQL that you expect.
0
 

Author Comment

by:movoni
ID: 13891695
People are beginning to repeat things here, so I'm going to request this question be closed and move on.  Obviously, I need to work on this a little more and post more of my code to clarify the question.  I cannot run Profiler as I do not have admin rights -- the SQL server is a shared server at a Web hosting company.

I'll come back when I can calrify more.  Thanks for the help thus far.

- MT
0
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 13898061
if you can't use profiler just output the sql string to the web page and read it. there is nothing wrong with the SQL query in any version you posted, I would look at the ASP.

then double check the values in the table you may have put it in incorrectly and mised a colon or slash or anything
0
 

Author Comment

by:movoni
ID: 13898128
Thanks andrew... do you mean the results of the SQL?  I know what that is... nothing ;)  I think where I'm frustrated is that it's tough to explain what's going on here without seeing it and it's on a password protected admin site, so I can't really point people to it.  I will try to post as much of the code as possible.
0
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 13898158
try

rsSimilar.Source = Replace(nested_rsSimilar, "12345", rsSearch.Fields.Item("URL").Value)

Response.Write  rsSimilar.Source
response.write "<p>--------------------</p>"
response.write rsSearch.Fields.Item("URL").Value
Response.End

then go carefully over the output or paste it back here for fresh eyes to see.

I am assuming this is some form of AS given the rs references. ASP is very probably messing up your
0
 
LVL 34

Expert Comment

by:arbert
ID: 13898218
"People are beginning to repeat things here, so I'm going to request this question be closed and move on.  Obviously, I need to work on this a little more and post more of my code to clarify the question.  I cannot run Profiler as I do not have admin rights -- the SQL server is a shared server at a Web hosting company."


That's a prime reason to test locally with MSDE or Developers edition.
0
 

Author Comment

by:movoni
ID: 13898272
I am not a professional developer with access to those tools.  Hence my EE subscription to get help when I need it (and it's been fantastic).  Again, I'm on a Web host doing this as a project for a succesful side business I have online.  Perhaps I'm in over my head now and need to hire it out.  Thankfully, this is an admin side issue that doesn't effect the functionality of the online service I'm offering.  More to come when I try Andrew's suggestion.
0
 

Author Comment

by:movoni
ID: 13899603
Ok guys, I beleive I've figured out how to ask this question... I think I asked it backwards.  The example SQL I am testing here is as follows:

SELECT * FROM dbo.NWL_LINKS WHERE URL LIKE 'http://www.test.com/Merchant2/merchant.mvc%'

The problem I have is that I need it to find any URL LIKE that.  So, I need it to find any shorter versions too.  Earlier, I was saying I needed to find the longer versions which is easy to do with a wildcard at the end.  So, the SQL above doesn't find http://www.test.com because the wildcard in the SQL comes AFTER... it can't go backwards.  Placing the % before the URL doesn't work (there's never anything before the http:// as my form adds that automatically to the database.

My appologies for not making this clear the first time around.

Thanks,
MT
0
 

Author Comment

by:movoni
ID: 13904788
It appears that there is no solution to this dilema.  ??
0
 
LVL 34

Accepted Solution

by:
arbert earned 2000 total points
ID: 13904857
Yes, shorten the string you're looking for.  You could either use LEFT to look for the first N characters of your sting, or you could use SUBSTRING to find the first SINGLE / and look for all the domains.
0
 

Author Comment

by:movoni
ID: 13904941
Can you provide a quick example for me?
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13904972
Like laotzi2000 first said......

It should work like this:
SELECT *
FROM LINKS
WHERE URL LIKE 'http://www.test.com%'



Bob
0
 

Author Comment

by:movoni
ID: 13904979
Using SUBSTRING.  Thanks everyone.  I'll try harder to be clearer next time.
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13904981
to avoid any more confusion.....
change
SELECT * FROM dbo.NWL_LINKS WHERE URL LIKE 'http://www.test.com/Merchant2/merchant.mvc%'

to
SELECT * FROM dbo.NWL_LINKS WHERE URL LIKE 'http://www.test.com%'

this is what laotzi2000  was suggesting.

Just tring to help clarify. Don't give me any points.

Bob
0
 
LVL 34

Expert Comment

by:arbert
ID: 13904985
We figured that out--the point is that if you do a like on a larger string, it's not going to work....

So, from your .NET or ASP code (whatever code you're using), you need to send a smaller string to the database, or, within your query do it....
0
 

Author Comment

by:movoni
ID: 13904986
Thanks Bob, but if you read the entire thread, you'll see the question changed a bit -- which was my fault.  I already had the wildcard at the end of the URL... I needed to find the shorter versions of it and SUBSTRING will do that.
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13905004
I wouldn't have know what laotzi2000 had first said if I hadn't read the entire thread. Anyway glad you got what you needed.

Bob
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13907094
SELECT *
FROM LINKS
WHERE URL LIKE 'http://www.test.co%'

Should find them both.
0
 

Author Comment

by:movoni
ID: 13915147
Just to close this out, after many hours of messing with the query, the SUBSTRING was the answer.  It was much more complex than I imagined, but it now works perfectly.

rsSimilar.Source = "SELECT     *  FROM         dbo.NWL_LINKS  WHERE     (SUBSTRING(LEFT(URL, CHARINDEX('//', URL) + 1) + LEFT(SUBSTRING(URL + '/', CHARINDEX('//', URL + '/') + 2, 255), CHARINDEX('/',                         SUBSTRING(URL + '/', CHARINDEX('//', URL + '/') + 2, 255)) - 1), CHARINDEX('/', URL) + 2, 255)                         LIKE SUBSTRING(LEFT('12345', CHARINDEX('//',                         '12345') + 1)                         + LEFT(SUBSTRING('12345' + '/', CHARINDEX('//',                         '12345' + '/') + 2, 255), CHARINDEX('/',                         SUBSTRING('12345' + '/', CHARINDEX('//',                         '12345' + '/') + 2, 255)) - 1), CHARINDEX('/',                         '12345') + 2, 255)) AND (URL <> '12345')  AND (ACTIVE = 1) ORDER BY LinkID, URL"

Thanks to all and again, sorry for the ranting and raving... I too was confused ;(
0
 
LVL 34

Expert Comment

by:arbert
ID: 13915239
Ya, looks like you over complicated it.  Why didn't you just send a shorter string from your application?
0
 

Author Comment

by:movoni
ID: 13915412
The form just sends a URL... it can be anything the user puts in there.  I didn't want them submitting multiple URLs from the same domain, but I didn't want to restrict them to just a domain (ie: www.test.com).  My admin app needed to lsit all similar URLs so I could compare during the approval process.  It's complicated to explain, but, nonetheless, this way works.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question