Wildcard Help

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
movoniAsked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
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
 
laotzi2000Commented:
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
 
movoniAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
nmcdermaidCommented:
I would recommend using SQL profiler to make sure that it is sending the SQL you think it is.
0
 
movoniAuthor Commented:
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
 
arbertCommented:
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
 
movoniAuthor Commented:
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
 
roshkmCommented:
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
 
movoniAuthor Commented:
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
 
arbertCommented:
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
 
movoniAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
nmcdermaidCommented:
And capture it in profiler!! I don't think its sending the SQL that you expect.
0
 
movoniAuthor Commented:
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
 
andrewbleakleyCommented:
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
 
movoniAuthor Commented:
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
 
andrewbleakleyCommented:
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
 
arbertCommented:
"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
 
movoniAuthor Commented:
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
 
movoniAuthor Commented:
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
 
movoniAuthor Commented:
It appears that there is no solution to this dilema.  ??
0
 
movoniAuthor Commented:
Can you provide a quick example for me?
0
 
Bob LambersonSoftware EngineerCommented:
Like laotzi2000 first said......

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



Bob
0
 
movoniAuthor Commented:
Using SUBSTRING.  Thanks everyone.  I'll try harder to be clearer next time.
0
 
Bob LambersonSoftware EngineerCommented:
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
 
arbertCommented:
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
 
movoniAuthor Commented:
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
 
Bob LambersonSoftware EngineerCommented:
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
 
nmcdermaidCommented:
SELECT *
FROM LINKS
WHERE URL LIKE 'http://www.test.co%'

Should find them both.
0
 
movoniAuthor Commented:
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
 
arbertCommented:
Ya, looks like you over complicated it.  Why didn't you just send a shorter string from your application?
0
 
movoniAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.