Hi Sebastian.
I have solved the problem with Danish characters. The method also works with French and German characters.
It’s a database problem – se later in this post!
I have a few new questions.
1 Can you recommend a book, where I can find how tables and stored procedures interact with SQL Express.
2 When adding or editing a post, the words won’t searchable until you have truncated a few tables and re-indexed the whole database. I would like that to be handled in a trigger.
------
I have found a function and incorporated into the stored procedure GetSearchResultsByWord
I have also edited the same stored procedure GetSearchResultsByWord
It now uses a like search to be able to search fractions off words. And I have added a replace characters to allow users – using wild cards.
To solve the problem follow this procedure:
Create a new function in Scalar-valued Functions
USE [DOTNETNUKE]
GO
/****** Object: UserDefinedFunction [dbo].[f_HTMLDecode] Script Date: 08/04/2010 12:21:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: aiken, Aged Yak Warrior, USA
-- Create date: 10/15/2002 : 15:54:50
-- Description: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20749
-- =============================================
ALTER FUNCTION [dbo].[f_HTMLDecode] (@vcWhat varchar(8000))
RETURNS varchar(8000) AS
BEGIN
DECLARE @vcResult varchar(8000)
DECLARE @vcCrLf varchar(2)
DECLARE @siPos smallint,@vcEncoded varchar(7),@siChar smallint
set @vcCrLF=char(13) + char(10)
select @vcResult=@vcWhat
select @siPos=PatIndex('%&#___;%',@vcResult)
WHILE @siPos>0
BEGIN
select @vcEncoded=substring(@vcResult,@siPos,6)
select @siChar=cast(substring(@vcEncoded,3,3) as smallint)
select @vcResult=replace(@vcResult,@vcEncoded,nchar(@siChar))
select @siPos=PatIndex('%&#___;%',@vcResult)
END
select @siPos=PatIndex('%&#____;%',@vcResult)
WHILE @siPos>0
BEGIN
select @vcEncoded=substring(@vcResult,@siPos,7)
select @siChar=cast(substring(@vcEncoded,3,4) as smallint)
select @vcResult=replace(@vcResult,@vcEncoded,nchar(@siChar))
select @siPos=PatIndex('%&#____;%',@vcResult)
END
select @vcResult=replace(@vcResult,'"','"')
select @vcResult=replace(@vcResult,'&','&')
select @vcResult=replace(@vcResult,'©','©')
select @vcResult=replace(@vcResult,'«','«')
select @vcResult=replace(@vcResult,'»','»')
select @vcResult=replace(@vcResult,'¼','¼')
select @vcResult=replace(@vcResult,'½','½')
select @vcResult=replace(@vcResult,'¿','¿')
select @vcResult=replace(@vcResult,'ë','ë')
select @vcResult=replace(@vcResult,'é','é')
select @vcResult=replace(@vcResult,'Æ','Æ')
select @vcResult=replace(@vcResult,'Ø','Ø')
select @vcResult=replace(@vcResult,'Å','Å')
select @vcResult=replace(@vcResult,'<P>',@vcCrLf)
return @vcResult
END
--------------------------------------------------------
--Alter your standard stored procedure GetSearchResultsByWord
USE [DOTNETNUKE]
GO
/****** Object: StoredProcedure [dbo].[GetSearchResultsByWord] Script Date: 08/04/2010 12:23:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSearchResultsByWord]
---- OLJ 20100717 added jokersearch
---- OLJ 20100802 added funcition f_HTMLDecode
@PortalID int,
@Word nvarchar(100)
AS
set @Word = dbo.f_HTMLDecode(@Word) --- OLJ 20100802 new function
SELECT
distinct --OLJ 20100718 distinct to avoid duplicates in the query result
si.SearchItemID,
sw.Word,
siw.Occurrences,
siw.Occurrences + 1000 AS Relevance,
m.ModuleID,
tm.TabID,
si.Title,
si.Description,
si.Author,
si.PubDate,
si.SearchKey,
si.Guid,
si.ImageFileId,
u.FirstName + ' ' + u.LastName As AuthorName,
m.PortalId
FROM dbo.SearchWord sw
INNER JOIN dbo.SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID
INNER JOIN dbo.SearchItem si ON siw.SearchItemID = si.SearchItemID
INNER JOIN dbo.Modules m ON si.ModuleId = m.ModuleID
LEFT OUTER JOIN dbo.TabModules tm ON si.ModuleId = tm.ModuleID
INNER JOIN dbo.Tabs t ON tm.TabID = t.TabID
LEFT OUTER JOIN dbo.Users u ON si.Author = u.UserID
WHERE (((m.StartDate Is Null) OR (GetDate() > m.StartDate)) AND ((m.EndDate Is Null) OR (GetDate() < m.EndDate)))
AND (((t.StartDate Is Null) OR (GetDate() > t.StartDate)) AND ((t.EndDate Is Null) OR (GetDate() < t.EndDate)))
AND (sw.Word LIKE '%' + REPLACE(REPLACE(@Word, '*', '%'),'?', '_')+ '%')-- OLJ 20100717 replaces the original line below
-- AND (sw.Word = @Word) -- OLJ 20100717 original statement
AND (t.IsDeleted = 0)
AND (m.IsDeleted = 0)
AND (t.PortalID = @PortalID)
AND (t.DisableLink = 0) -- OLJ 20100717
OR (m.ModuleTitle LIKE '%' + REPLACE(REPLACE(@Word, '*', '%'),'?', '_')+ '%')-- OLJ 20100717 replace characters
ORDER BY Relevance DESC