| Author |
Topic |
|
blahblah
Starting Member
6 Posts |
 Posted - 2010-07-27 : 05:14:37
|
| hi,i have a pattern matching question.if my data is as follows:"dispenser catalogue"i want to match a word. i would ideally like the search phrase of "pen" not to return the dispenser... ie only the exact word dispenser should match.So looks like i can't use the %pen%is there a way to ensure that only full word matches are returned? |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-27 : 05:20:25
|
| where yourcolumn='yourword'Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
blahblah
Starting Member
6 Posts |
Posted - 2010-07-27 : 06:42:38
|
| hi,yes but that is a pure column match isn't it?i have a column with 'dispenser catalogue'and i want to match the exact word 'dispenser'not pen, not penser and not disp.. but dispenser exactlyor 'catalogue', not 'cat', catal etc |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-27 : 06:45:28
|
| where yourcolumn='dispenser%'Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
blahblah
Starting Member
6 Posts |
Posted - 2010-07-27 : 07:26:19
|
| that works for the first word only i think.if i search for 'catalogue%' it won't return anything.and if i search for '%cat%' i'm again returned with an incorrect set.in terms of a column pattern match the only solution i came up with was to ensure the string column has spaces in between and then include this space in the pattern match (ie '% catalogue %') so that i get the full word. but this means i have to change the data to ensure the field has spaces before and after (' dispenser catalogue ')i was hoping for something a little more complex possibly with regexp's or something.fyi i'm running sql 2000the search field is text.thanks! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-27 : 09:26:34
|
regexp's not in sql server.But what about this:where ' '+column+' ' like '% searchedword %'
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
blahblah
Starting Member
6 Posts |
Posted - 2010-07-27 : 09:47:49
|
| Hi,Yes i have the '% srch %' in place as i said for the whole word match (presuming its seperated by spaces)What does the ' ' + column + ' ' do?the syntax does not parse |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-27 : 09:54:00
|
column should be replaced with the name of your column in your table.' '+ column + ' ' makes "dispenser catalogue" to " dispenser catalogue " and so the like '% dispenser %' should work.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
blahblah
Starting Member
6 Posts |
Posted - 2010-07-27 : 10:00:18
|
| ah yes, sorry the syntax failed due to it being a text field so I needed to convert the column type.ok thanks for your help, i thought there might be a jazy way for sql to handle this but apparently not.thanks! |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-08-02 : 11:56:10
|
| declare @SearchTerm varchar(50)set @SearchTerm = 'pen'select ....from ....where ' ' + SearchColumn + ' ' like '% ' + @SearchTerm + ' %'________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
blahblah
Starting Member
6 Posts |
Posted - 2010-08-03 : 03:35:35
|
quote:
Originally posted by blindman it is practically useless.________________________________________________
|
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-08-03 : 09:57:05
|
quote:
Originally posted by blahblah
quote:
Originally posted by blindman it is practically useless.________________________________________________
Clarify?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
pattern matching