Search issue with Arabic or any Unicode characters

Jul 30, 2009 at 12:17 PM
Edited Jul 30, 2009 at 12:37 PM

 

Hello,

First I appreciate you for creating such free and powerful Store for DNN.

Only thing I just noticed is not working properly on your module is the search product for Arabic and Unicode characters. It's all in a Stored Procedure named: NEvoweb_NB_Store_Products_GetSearchList
but I could not fix it.

 

It should be a couple of lines listed below:

set @SQL = @SQL + ' AND (difference(''' + @Word + ''',substring(sw.Word,1,len(''' + @Word + '''))) + difference(''' + @Word + ''',substring(sw.Word,len(sw.Word)-len(''' + @Word + ''')+1,len(sw.Word)))) >= 7 '
    set @SQL = @SQL + ' AND substring(soundex(sw.Word),1,1) = substring(''' + @Word + ''',1,1)'

 

I directly added a value for @Word, It works correctly for English letters but not for Arabic letter:

set @Word=N'آموزش'

I also added a N before @Word to declare the Nvarchar but it did not help:

set @SQL = @SQL + ' AND (difference(N''' + @Word + ''',substring(sw.Word,1,len(N''' + @Word + '''))) + difference(N''' + @Word + ''',substring(sw.Word,len(sw.Word)-len(N''' + @Word + ''')+1,len(sw.Word)))) >= 7 '
    set @SQL = @SQL + ' AND substring(soundex(sw.Word),1,1) = substring(N''' + @Word + ''',1,1)'

 

Thanks in Advanced

 

Coordinator
Jul 30, 2009 at 1:37 PM

Hi,

Nice to see other people struggling with language stuff..

I noticed this in the last version....I was trying to be too clever with soundex and partial searches in order to overcome the problems of accents  in langauges.

In the last release v1.1.7 (beta) I've changed the way this works and made it more simple:

set @SQL = @SQL + ' AND sw.Word collate Latin1_General_CI_AI like ''%' + @Word + '%'''

The thing to notice here is that I use the "collate Latin1_General_CI_AI" to remove accents from the word I search for, obviously in Arabic letter system this may not work either, but you may be able to adapt the the SPROC to either remove the "collate Latin1_General_CI_AI" or change it to something omore suitable for the Arabic letter system.

I hope this helps,

Regards,

Dave.

 

 

Mar 22, 2011 at 10:08 AM
Edited Mar 23, 2011 at 8:34 PM

Current version is 2.2.1.

I found the above procedure and changed SQL_Latin1_General_CP1_CI with Cyrillic_General_CI_AS

But still I can't search unicode characters.


USE [hristope_base]
GO
/****** Object:  StoredProcedure [dbo].[NEvoweb_NB_Store_Products_GetSearchList]    Script Date: 03/23/2011 22:07:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
Author: DCL
Last Modified for NB_Store version: 02.00.08
*/
ALTER    PROCEDURE [dbo].[NEvoweb_NB_Store_Products_GetSearchList]
@PortalID int,
@Lang char(5),
@Word nvarchar(100),
@OrderBy nvarchar(50),
@OrderDESC bit,
@ReturnLimit nvarchar(5),
@PageIndex int,
@PageSize int
AS
begin

	select distinct top 200 si.searchkey into #SearchTemp
	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.[NB_Store_Products] as P on si.SearchKey = (convert(nvarchar(10),P.ProductID) + @Lang) 
	inner join dbo.NB_Store_ProductLang as PL on P.ProductID = PL.ProductID and PL.Lang = @Lang  
	WHERE sw.Word collate Cyrillic_General_CI_AS = @Word 
    and P.PortalID = @PortalID

	declare @SQL nvarchar(4000)

if @ReturnLimit=0 
begin
	set @ReturnLimit=200
end

set @SQL = ' SELECT '

if @ReturnLimit > 0 
begin
	set @SQL = @SQL + ' top ' + @ReturnLimit
end

 set @SQL = @SQL + ' P.ProductID,'
 set @SQL = @SQL + ' P.PortalID,'
 set @SQL = @SQL + ' P.TaxCategoryID,'
 set @SQL = @SQL + ' P.Featured,'
 set @SQL = @SQL + ' P.Archived,'
 set @SQL = @SQL + ' P.CreatedByUser,'
 set @SQL = @SQL + ' P.CreatedDate,'
 set @SQL = @SQL + ' P.ModifiedDate,'
 set @SQL = @SQL + ' P.IsDeleted,'
 set @SQL = @SQL + ' P.ProductRef,'
 set @SQL = @SQL + ' PL.Lang,'
 set @SQL = @SQL + ' PL.Summary,'
 set @SQL = @SQL + ' PL.Description,'
 set @SQL = @SQL + ' PL.Manufacturer,'
 set @SQL = @SQL + ' dbo.NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID) as FromPrice,'
 set @SQL = @SQL + ' isnull((select sum(QtyRemaining) from dbo.NB_Store_Model where productid = P.ProductID and qtyremaining != 0),0) as QtyRemaining,'
 set @SQL = @SQL + ' isnull((select sum(QtyStockSet) from dbo.NB_Store_Model where productid = P.ProductID),0) as QtyStockSet,'
 set @SQL = @SQL + ' (select top 1 ImageID from dbo.NB_Store_ProductImage AS I where productid = P.ProductID order by listorder) as ImageID,'
 set @SQL = @SQL + ' (select top 1 ImageURL from dbo.NB_Store_ProductImage AS I where productid = P.ProductID order by listorder) as ImageURL,'
 set @SQL = @SQL + ' (select top 1 ImageDesc from dbo.NB_Store_ProductImage AS I inner join dbo.NB_Store_ProductImageLang as IL on IL.ImageID = I.ImageID and IL.Lang = PL.Lang where productid = P.ProductID order by listorder) as ImageDesc,'
 set @SQL = @SQL + ' PL.ProductName, '
 set @SQL = @SQL + ' PL.[XMLData]'


set @SQL = @SQL + ' from dbo.[NB_Store_Products] P '
	set @SQL = @SQL + ' inner join dbo.NB_Store_ProductLang as PL on P.ProductID = PL.ProductID and PL.Lang = ''' + @Lang + '''' 
set @SQL = @SQL + ' WHERE ((convert(nvarchar(10),P.ProductID) + ''' + @Lang + ''') in (select SearchKey from #searchTemp)) '
	set @SQL = @SQL + ' and P.PortalID = ' + convert(nvarchar(5),@PortalID)
	set @SQL = @SQL + ' and P.Isdeleted = 0 '
	set @SQL = @SQL + ' and P.Archived = 0 '


	if @OrderBy='man'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.Manufacturer DESC,P.ProductRef DESC,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by PL.Manufacturer,P.ProductRef,PL.ProductName'
	end
	if @OrderBy='ref'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by P.ProductRef DESC,PL.Manufacturer DESC,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by P.ProductRef,PL.Manufacturer,PL.ProductName'
	end
	if @OrderBy='name'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by PL.ProductName DESC,PL.Manufacturer DESC,P.ProductRef DESC'
		else
			set @SQL = @SQL + ' order by PL.ProductName,PL.Manufacturer,P.ProductRef'
	end
	if @OrderBy='price'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by dbo.NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID) DESC,PL.Manufacturer DESC,P.ProductRef DESC,PL.ProductName DESC'
		else
			set @SQL = @SQL + ' order by dbo.NEvoWeb_NB_Store_FGetProductBasePrice(P.ProductID),PL.Manufacturer,P.ProductRef,PL.ProductName'
	end
	if @OrderBy='cdate'
	begin
		if @OrderDESC=1
			set @SQL = @SQL + ' order by P.CreatedDate DESC'
		else
			set @SQL = @SQL + ' order by P.CreatedDate'
	end
	if @OrderBy=''
	begin
		set @SQL = @SQL + ' order by PL.ProductName,PL.Manufacturer,P.ProductRef'
	end

exec sp_executesql @SQL

drop table #SearchTemp
 
end



Coordinator
Mar 28, 2011 at 7:04 AM
Edited Mar 28, 2011 at 7:09 AM

Hi hotris,

Sorry about the late reply.  With v2.2.1 the "getsearchlist" SPROC has been replaced.  The search now uses the same SPROC as the other functions to get data.

Try using the "COLLATE Cyrillic_General_CI_AI" on the "NEvoweb_NB_Store_Products_GetList" SPROC.

Although using the kind of collation does only give the search exclusing the accents, so you might need to delve a little more to get exactly what you require.

Regards.

Dave

Coordinator
Mar 28, 2011 at 7:09 AM

HI Sorry,

I wrote tyhe wrong SPROC..... it should be "NEvoweb_NB_Store_Products_GetList".

Dave.

Mar 28, 2011 at 7:06 PM

Thank you very much for the reply but still I can't solve the problem. I'm not very good in the matter.

Simply replacing Latin1_General_CI_AI to Cyrillic_General_CI_AI in many places, don't work.

What kind of delving I have to do?

Coordinator
Mar 28, 2011 at 10:13 PM

hi hotris,

Unfortunatly I've only had experiance with latin collation, so I'm unsure what your going to have to do to get NB_Store working with you character set.

I assume you've got your DB setup correctly for your language?

Only suggestion I have is to review the documentation for SQL Server http://msdn.microsoft.com/en-us/library/ms143503.aspx

Is your problem just with the search or have you got a DB storage problem with your charecter set?

Regards,

Dave