sinon, voici une petite fonction qui te permet d'avoir un soundhex sur des mots plus grands :
CREATE FUNCTION big_soundex
(@WordString varchar(100), @SoundExLen int)
RETURNS varchar(100)
AS
begin
declare @Counter int
declare @CurrChar char(1)
declare @retVal varchar(100)
-- test de la longueur demandé
If @SoundExLen >= 100
begin
set @SoundExLen = 100
end
if @SoundExLen < 4
begin
set @SoundExLen = 4
end
set @SoundExLen = @SoundExLen - 1
-- soundex ne travail qu'avec des lettres capitale
set @WordString = upper(@WordString)
SET @Counter = 1
--on parcours la chaine et on remplace tout ce qui n'est pas une lettre par un blanc
WHILE @Counter <= len(@WordString)
BEGIN
If ASCII(SUBSTRING(@WordString, @Counter, 1)) < 65 Or ASCII(SUBSTRING(@WordString, @Counter, 1)) > 90
BEGIN
set @WordString = Left(@WordString, @Counter - 1) + ' ' + SUBSTRING(@WordString, @Counter + 1, len(@WordString))
End
set @counter = @counter + 1
END
--suppression des eventuels espace en debut et en fin de chaine
set @WordString = lTrim(rtrim(@WordString))
--création du 'son'
If Len(lTrim(rtrim(@WordString))) = 0
begin
set @retVal = ''
end
Else
begin
set @retVal = @WordString
set @retVal = Replace(@retVal, 'A', '0')
set @retVal = Replace(@retVal, 'E', '0')
set @retVal = Replace(@retVal, 'I', '0')
set @retVal = Replace(@retVal, 'O', '0')
set @retVal = Replace(@retVal, 'U', '0')
set @retVal = Replace(@retVal, 'Y', '0')
set @retVal = Replace(@retVal, 'H', '0')
set @retVal = Replace(@retVal, 'W', '0')
set @retVal = Replace(@retVal, 'B', '1')
set @retVal = Replace(@retVal, 'P', '1')
set @retVal = Replace(@retVal, 'F', '1')
set @retVal = Replace(@retVal, 'V', '1')
set @retVal = Replace(@retVal, 'C', '2')
set @retVal = Replace(@retVal, 'S', '2')
set @retVal = Replace(@retVal, 'G', '2')
set @retVal = Replace(@retVal, 'J', '2')
set @retVal = Replace(@retVal, 'K', '2')
set @retVal = Replace(@retVal, 'Q', '2')
set @retVal = Replace(@retVal, 'X', '2')
set @retVal = Replace(@retVal, 'Z', '2')
set @retVal = Replace(@retVal, 'D', '3')
set @retVal = Replace(@retVal, 'T', '3')
set @retVal = Replace(@retVal, 'L', '4')
set @retVal = Replace(@retVal, 'M', '5')
set @retVal = Replace(@retVal, 'N', '5')
set @retVal = Replace(@retVal, 'R', '6')
set @CurrChar = Left(@retVal, 1)
set @counter = 2
--on retire les son successif iddentique
while @counter <= len(@retVal)
begin
If substring(@retVal, @Counter, 1) = @CurrChar
begin
set @retVal = Left(@retVal, @Counter - 1) + substring(@retVal, @Counter + 1, len(@retVal))
end
Else
begin
set @CurrChar = SUBSTRING(@retVal, @Counter, 1)
End
set @counter = @counter + 1
end
--suppression des blanc
set @retVal = Replace(@retVal, ' ', '')
--suppression des 0
set @retVal = substring(@retVal, 2, len(@retVal))
set @retVal = Replace(@retVal, '0', '')
-- on ajoute des 0 si le 'son' est trop court pour etre sur davoir le nombre de caractère voulue
set @retVal = @retVal + REPLICATE('0', @SoundExLen)
set @retVal = Left(@WordString, 1) + Left(@retVal, @SoundExLen)
End
-- retour du son
return @retVal
end
