Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
niemeyer
Starting Member
15 Posts |
 Posted - 2003-06-02 : 09:27:45
|
| I'm working with some French data and expose a search facility (SQL 2000) on the site. When searching for products one may enter a keyword from the title or description field (using the LIKE condition), but how can I map the characters so when one enters'Napoleon' it also returns products with 'Napoléon' (with the accented e) in the title field? Another little issue: Is it possible to have a loose match so when entering 'Claudius' in the search box it also returns everything with 'Claude' in the title field? It works OK with 'LIKE' as long as you stop by 'Claud'...I'm quite new to SQL server so thanks very much in advance for any suggestions.// Niels, Brussels |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-02 : 10:30:34
|
| try using a collation for the accent.look at collate in bol.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-02 : 10:42:27
|
| You might want to consider SOUNDEX to find 'sounds like' matches.Sam |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-02 : 11:45:38
|
quote:
try using a collation for the accent.look at collate in bol.
Thanks for the suggestion. Could you elaborate a bit on that? Can I do this in general for the server instance or can I do it in the .vb script block? Could one map some wildcards in the condition like [eé],[aá] etc.Thanks again,Niels |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-02 : 12:18:25
|
quote:
You might want to consider SOUNDEX to find 'sounds like' matches.Sam
Good idea! The SOUNDEX and DIFFERENCE could maybe solve both my problems... How do you handle it practically (didn't find much info on how to implement these functions in bol).. I have the following in my productcatalog.vb:...Dim whereClause As String = "SELECT ProductID,Lot,LotID,SKU,Title,Summary,Description,Grading,GradingGB,Featured,ProductImages,UnitCost,Discount,UnitCost-Discount As UnitCostwDiscount FROM Products WHERE " Dim aTerms() As String = Nothing aTerms = toSearch.Split(New Char(){" "c})Select Case searchWhat Case SearchWhatEnum.SearchLot whereClause += "LotID" + " = '" + toSearch + "'" Case SearchWhatEnum.SearchTitle whereClause += "Title" + " LIKE '%" + String.Join("%' OR " + "Title" + " LIKE '%", aTerms) + "%'" Case SearchWhatEnum.SearchDescription whereClause += "Description" + " LIKE '%" + String.Join("%' OR " + "Description" + " LIKE '%", aTerms) + "%'" End Select...How can I build the SOUNDEX in here? |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-02 : 21:47:07
|
quote:
try using a collation for the accent.look at collate in bol.
Solved the accent problem: SELECT Title FROM Products WHERE Title COLLATE French_CI_AI LIKE '%napoleon%'Thanks for directing me to the collation issue!// Niels |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-02 : 23:29:19
|
| If a collation should always apply for a specific field in a table, then I found that the easiest way was to open the table in EM and then open the Collation dialog box for that field and select Windows French with 'Case Sensitive' and 'Accent Sentive' unchecked.//Niels |
|
|
|
|
|
|
|
|
Newbie question on character mappings