SQL + Function

[Jean Philippe]

Hi, I've just created a little function in the module of Access to
remplace some caracteres. So when I used it directly in Access no
problem, but when I want to use the same request from asp with SQL I
get an error who is saying that Undefined function 'remplaceAccents'
in expression.

See below the SQL string and the function

SELECT remplaceAccents([Liste_TAB.Prenom]) AS Prenom,
remplaceAccents([Liste_TAB.Nom]) AS Nom
FROM Liste_TAB;


Function remplaceAccents(strTrie)
Dim strIllegal, strlegal
' Liste des caracteres illégaux
**************************************************
*****
strIllegal = Array("'", "é", "Á", "Â", "Ã", "Ä", "Å", "Æ", "Ç", "È",
"É", "Ê", "Ë", "Ì", "Í", "Î", "Ï", "Ñ", "Ò", "Ó", "Ô", "Õ", "Ö", "Ù",
"Ú", "Û", "Ü", "Ý", "à", "á", "â", "ã", "ä", "å", "æ", "ç", "è", "é",
"ê", "ë", "ì", "í", "î", "ï", "ð", "ñ", "ò", "ó", "ô", "õ", "ö", "ù",
"ú", "û", "ü", "ý", "ÿ")
' Liste des caracteres que nous voulons remplacer
*************************************
strlegal = Array("''", "e", "A", "A", "A", "A", "A", "ae", "C", "e",
"e", "e", "e", "i", "i", "i", "i", "N", "O", "O", "O", "O", "O", "U",
"U", "U", "U", "Y", "a", "a", "a", "a", "a", "a", "ae", "c", "e", "e",
"e", "e", "i", "i", "i", "i", "o", "n", "o", "o", "o", "o", "o", "u",
"u", "u", "u", "y", "y")

For i = 0 To UBound(strIllegal)
strTrie = Replace(strTrie, strIllegal(i), strlegal(i))
Next

remplaceAccents = strTrie
End Function

Hope someone will have the answer.
Thanks
JP

 

[Joe Fallon]

Right.
So you have to re-write the function inside SQL Server and call it in a
similar fashion.
SQL Server supports UDF (User Defined Functions.)
The call requires the owner (usually dbo) as the prefix.
e.g.
SELECT dbo.remplaceAccents([Liste_TAB.Prenom]) AS Prenom,
dbo.remplaceAccents([Liste_TAB.Nom]) AS Nom
FROM Liste_TAB