pietjepuk
asked on
Greek Query part 2
In my table an ntext field contains mixed language data, english, german and greek.
Running a query with greek like
select engelszoektekst from lemma where engelszoektekst like N'% ***greek with diacritics here*** %'
works just fine.
If I replace characters with diacritics by basic characters (i.e. alpha for all alpha variants) i get no result. Using accent insensitive colation like
select engelszoektekst from lemma where engelszoektekst like N'% ***greek without diacritics here*** %' collate SQL_Latin1_General_Cp1253_
Any ideas?
Running a query with greek like
select engelszoektekst from lemma where engelszoektekst like N'% ***greek with diacritics here*** %'
works just fine.
If I replace characters with diacritics by basic characters (i.e. alpha for all alpha variants) i get no result. Using accent insensitive colation like
select engelszoektekst from lemma where engelszoektekst like N'% ***greek without diacritics here*** %' collate SQL_Latin1_General_Cp1253_
Any ideas?
ASKER
in ASP, you can use this function :
Public Function removeAccent(source)
avantConversion = "àÀâÂäÄáÁéÉèÈêÊëËìÌîÎïÏòÒô
apresConversion = "aAaAaAaAeEeEeEeEiIiIiIoOo
temp = source
For boucle = 1 To Len(avantConversion)
temp = Replace(temp, Mid(avantConversion, boucle, 1),
Mid(apresConversion, boucle, 1))
Next
temp = Replace(temp, "œ", "oe")
removeAccent = temp
End Function
You can create this function directly in sql server like this :
CREATE FUNCTION replaceAccentChar (@source as varchar(255))
RETURNS varchar(255) AS
BEGIN
declare @charList as varchar(20)
declare @temp as varchar(255)
declare @t as int
set @temp = @source
set @charList = 'aeioucn'
set @t = 0
while @t <= len(@charList)
begin
set @temp = replace(@temp, substring(@charList, @t, 1),
substring(@charList, @t, 1))
set @t = @t + 1
end
set @temp = Replace(@temp, 'œ', 'oe')
set @temp = Replace(@temp, '’', '''')
return @temp
END
Public Function removeAccent(source)
avantConversion = "àÀâÂäÄáÁéÉèÈêÊëËìÌîÎïÏòÒô
apresConversion = "aAaAaAaAeEeEeEeEiIiIiIoOo
temp = source
For boucle = 1 To Len(avantConversion)
temp = Replace(temp, Mid(avantConversion, boucle, 1),
Mid(apresConversion, boucle, 1))
Next
temp = Replace(temp, "œ", "oe")
removeAccent = temp
End Function
You can create this function directly in sql server like this :
CREATE FUNCTION replaceAccentChar (@source as varchar(255))
RETURNS varchar(255) AS
BEGIN
declare @charList as varchar(20)
declare @temp as varchar(255)
declare @t as int
set @temp = @source
set @charList = 'aeioucn'
set @t = 0
while @t <= len(@charList)
begin
set @temp = replace(@temp, substring(@charList, @t, 1),
substring(@charList, @t, 1))
set @t = @t + 1
end
set @temp = Replace(@temp, 'œ', 'oe')
set @temp = Replace(@temp, '’', '''')
return @temp
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER