| Author |
Topic |
|
tomat
Starting Member
3 Posts |
 Posted - 2005-11-23 : 05:12:22
|
| hi, I have a column (varchar) with numeric comma separated value in it (a number value, ex: 1,2,3 etc)what is the best way to do searching on this column?I found that full text search with CONTAINS seems not working, Tommy |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 05:37:46
|
| >>I have a column (varchar) with numeric comma separated value in it (a number value, ex: 1,2,3 etc)Why do you have that format?Make your table properly normalised so that it is easy for data manipulationsWhat does this query return?Select Columns from yourTable where varchar_column like 'someNumber'MadhivananFailing to plan is Planning to fail |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-23 : 05:42:13
|
| you can use like for that matter... Select <Column_Names> From <TableName> Where Column_Name like '%Number%'...???Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
tomat
Starting Member
3 Posts |
Posted - 2005-11-24 : 06:38:26
|
thanks for the feedback,Some background on this problem:My varchar column is not design to be searchable in the beginning; the data comes from HTML multiple selections, ex: 1,4,7,9 etc. so I just save it right away to DB without normalizing it and I have an ASP function to deal with it. Later there is a need to search on this column for some reason, so I thought I could use full-text search function but I end-up with all being noise words,  Currently I'm using LIKE '%<number>%' to deal with that problem, while I'm normalizing the data. Thanks for the feedback and comments Tommy |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-24 : 07:42:15
|
| Did that method work correctly?MadhivananFailing to plan is Planning to fail |
|
|
|
tomat
Starting Member
3 Posts |
Posted - 2005-11-25 : 05:42:39
|
| It’s not perfect, but enough for simple search.If you search for 1 it will match: 1,15,51 or any number with 1, etc. tommy |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2005-11-25 : 05:51:22
|
| U can try this Select <Column_Names> From <TableName> Where replace(Column_Name,','+Column_Name+',') like '%,'+Number+',%' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-25 : 08:46:12
|
| "Select <Column_Names> From <TableName> Where replace(Column_Name,','+Column_Name+',') like '%,'+Number+',%'"I don't think you meant to use REPLACE did you?Select <Column_Names> From <TableName>Where ','+Column_Name+',' like '%,1,%' -- assuming you are looking for "1"Kristen |
|
|
|
|
Searching comma separated value