Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > Microsoft SQL Server
Reload this Page

SQL Exact Match

Discuss SQL Exact Match in the Microsoft SQL Server forum on ASP Free.
SQL Exact Match Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!
  #1  
Old November 30th, 2007, 09:55 AM
webfort webfort is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 230 webfort User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 30 m 35 sec
Reputation Power: 5
SQL Exact Match
Is there away or function already built in to MS SQL 2000 that will get an exact match for a word. I am using the wild card % % on a search term but this returns words that contain that value also. example:-

If i look for the word "like" it will return results which contain "like" and and "likely". But I'm only looking for the word "like".

Thanks
Reply With Quote
  #2  
Old November 30th, 2007, 11:04 AM
Lauramc's Avatar
Lauramc Lauramc is offline
SQL Slarentice
ASP Free Intermediate (1500 - 1999 posts)
  
Join Date: Nov 2004
Location: In My Happy Place
Posts: 1,782 Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 15 h 42 m 2 sec
Reputation Power: 1081
Well it all depends on what you mean by exact match. If you mean that the only word in the field should be 'like' then you can say WHERE some_field = 'Like'. If you mean that the word can be anywhere in that field, then you could try your query without the % and see what you get. If that does not work, try adding a space after the word (provided that you are confident there should be a space there), such as WHERE some_field LIKE 'Like '.

Ultimately what works for you will have to be decided with some testing. I'm afraid there is no magic bullet for this.
__________________
Slarentice (origin:Shadow Wizard of ASP Free) [noun] A slave and apprentice of the Wizard's Circle (specifically of mehere) at ASP Free.
----
If shemzilla takes over, it's best to be on his good side

Reply With Quote
  #3  
Old November 30th, 2007, 04:26 PM
webfort webfort is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 230 webfort User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 30 m 35 sec
Reputation Power: 5
Quote:
Originally Posted by Lauramc
Well it all depends on what you mean by exact match. If you mean that the only word in the field should be 'like' then you can say WHERE some_field = 'Like'. If you mean that the word can be anywhere in that field, then you could try your query without the % and see what you get. If that does not work, try adding a space after the word (provided that you are confident there should be a space there), such as WHERE some_field LIKE 'Like '.

Ultimately what works for you will have to be decided with some testing. I'm afraid there is no magic bullet for this.


Thanks for your reply, I'ved tried your suggestion and it doesn't bring any results back is there any other way?
Reply With Quote
  #4  
Old November 30th, 2007, 05:03 PM
Lauramc's Avatar
Lauramc Lauramc is offline
SQL Slarentice
ASP Free Intermediate (1500 - 1999 posts)
  
Join Date: Nov 2004
Location: In My Happy Place
Posts: 1,782 Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 15 h 42 m 2 sec
Reputation Power: 1081
Quote:
Originally Posted by webfort
Thanks for your reply, I'ved tried your suggestion and it doesn't bring any results back is there any other way?


I did some more research, and I think I found a solution. Try running this test and you will see what I mean:
sql Code:
Original - sql Code 
  1. DECLARE @Tab TABLE
  2. (
  3.     [id] int,
  4.     [test] varchar(100)
  5. )
  6. INSERT INTO @Tab
  7. VALUES (1, 'This is a test searching for like.')
  8.  
  9. INSERT INTO @Tab
  10. VALUES (2, 'This is a test for alike messages.')
  11.  
  12. INSERT INTO @Tab
  13. VALUES (3, 'It is likely the effort will fail.')
  14.  
  15. INSERT INTO @Tab
  16. VALUES (4, 'I like rainy days.')
  17.  
  18. SELECT * FROM @Tab WHERE test LIKE '%[^a-z]like[^a-z]%'
The [^a-z] says that the characters before and after must not be letters a-z. I hope this solves it for you!
Comments on this post
webfort agrees: Great, works really well.
Reply With Quote
  #5  
Old December 1st, 2007, 10:25 AM
webfort webfort is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 230 webfort User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 30 m 35 sec
Reputation Power: 5
Quote:
Originally Posted by Lauramc
I did some more research, and I think I found a solution. Try running this test and you will see what I mean:
sql Code:
Original - sql Code 
  1. DECLARE @Tab TABLE
  2. (
  3.     [id] int,
  4.     [test] varchar(100)
  5. )
  6. INSERT INTO @Tab
  7. VALUES (1, 'This is a test searching for like.')
  8.  
  9. INSERT INTO @Tab
  10. VALUES (2, 'This is a test for alike messages.')
  11.  
  12. INSERT INTO @Tab
  13. VALUES (3, 'It is likely the effort will fail.')
  14.  
  15. INSERT INTO @Tab
  16. VALUES (4, 'I like rainy days.')
  17.  
  18. SELECT * FROM @Tab WHERE test LIKE '%[^a-z]like[^a-z]%'
The [^a-z] says that the characters before and after must not be letters a-z. I hope this solves it for you!


Thanks that works a treat, I imagine a few people will find this thread very useful, thanks again
Reply With Quote
  #6  
Old December 4th, 2007, 03:45 PM
webfort webfort is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 230 webfort User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 30 m 35 sec
Reputation Power: 5
Quote:
Originally Posted by Lauramc
I did some more research, and I think I found a solution. Try running this test and you will see what I mean:
sql Code:
Original - sql Code 
  1. DECLARE @Tab TABLE
  2. (
  3.     [id] int,
  4.     [test] varchar(100)
  5. )
  6. INSERT INTO @Tab
  7. VALUES (1, 'This is a test searching for like.')
  8.  
  9. INSERT INTO @Tab
  10. VALUES (2, 'This is a test for alike messages.')
  11.  
  12. INSERT INTO @Tab
  13. VALUES (3, 'It is likely the effort will fail.')
  14.  
  15. INSERT INTO @Tab
  16. VALUES (4, 'I like rainy days.')
  17.  
  18. SELECT * FROM @Tab WHERE test LIKE '%[^a-z]like[^a-z]%'
The [^a-z] says that the characters before and after must not be letters a-z. I hope this solves it for you!


I was wondering if you could help me again, I've done some further testing and if like is the only word that appears it doesn't match it, if I put a space before it, it works, is there away around this, if not could you point me in the right direction? Thanks
Reply With Quote
  #7  
Old December 4th, 2007, 04:20 PM
Lauramc's Avatar
Lauramc Lauramc is offline
SQL Slarentice
ASP Free Intermediate (1500 - 1999 posts)
  
Join Date: Nov 2004
Location: In My Happy Place
Posts: 1,782 Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 15 h 42 m 2 sec
Reputation Power: 1081
Quote:
Originally Posted by webfort
I was wondering if you could help me again, I've done some further testing and if like is the only word that appears it doesn't match it, if I put a space before it, it works, is there away around this, if not could you point me in the right direction? Thanks
Yes I see what you mean. Here is how I solved that:
sql Code:
Original - sql Code 
  1. DECLARE @Tab TABLE(
  2.     [id] int,
  3.     [test] varchar(100))
  4. INSERT INTO @Tab
  5. VALUES (1, 'This is a test searching for like.') 
  6. INSERT INTO @Tab
  7. VALUES (2, 'This is a test for alike messages.') 
  8. INSERT INTO @Tab
  9. VALUES (3, 'It is likely the effort will fail.') 
  10. INSERT INTO @Tab
  11. VALUES (4, 'I like rainy days.') 
  12. INSERT INTO @Tab
  13. VALUES (5, 'like')
  14. INSERT INTO @Tab
  15. VALUES (6, 'I like')
  16. INSERT INTO @Tab
  17. VALUES (7, 'like hi.')
  18.  
  19. SELECT * 
  20. FROM @Tab 
  21. WHERE 
  22.     test LIKE '%[^a-z]like[^a-z]%' OR 
  23.     test LIKE 'like[^a-z]%' OR 
  24.     test LIKE '%[^a-z]like' OR 
  25.     test = 'like'
I believe this covers all possibilities. I know it's lengthy, but it does seem to work!
Reply With Quote
  #8  
Old December 5th, 2007, 11:12 AM
webfort webfort is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 230 webfort User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 5 h 30 m 35 sec
Reputation Power: 5
Quote:
Originally Posted by Lauramc
Yes I see what you mean. Here is how I solved that:
sql Code:
Original - sql Code 
  1. DECLARE @Tab TABLE(
  2.     [id] int,
  3.     [test] varchar(100))
  4. INSERT INTO @Tab
  5. VALUES (1, 'This is a test searching for like.') 
  6. INSERT INTO @Tab
  7. VALUES (2, 'This is a test for alike messages.') 
  8. INSERT INTO @Tab
  9. VALUES (3, 'It is likely the effort will fail.') 
  10. INSERT INTO @Tab
  11. VALUES (4, 'I like rainy days.') 
  12. INSERT INTO @Tab
  13. VALUES (5, 'like')
  14. INSERT INTO @Tab
  15. VALUES (6, 'I like')
  16. INSERT INTO @Tab
  17. VALUES (7, 'like hi.')
  18.