If "eleph" is the complete value in the column you can the Switch function to
return "elephant" for "eleph". You can have multiple expressions, so if you
wanted to return "Hippopotamus" for "Hippo" as well, you might have
something like this:
SELECT [AnimalID], [Habitat],
SWITCH([Animal] = "Eleph","Elephant", [Animal] = "Hippo","Hippopotamus",
TRUE,[Animal]) AS [AnimalName]
FROM [Animals];
By using the Boolean constant TRUE for the final expression, if any of the
earlier ones in the list don't evaluate to TRUE then the value in the Animal
column would be returned.
If "eleph" is a substring within the column's value then you can use the
Replace function, but this limits you to replacing one particular substring:
SELECT [AnimalID], [Habitat],
REPLACE([Animal],"eleph","elephant") AS [AnimalName]
FROM [Animals];
If you wanted to replace both "eleph" and "hippo" substrings for instance
then you could write a simple VBA function which calls the Replace function
twice and returns a new string with both values replaced:
Public Function ReplaceValues(strOldValue As String) As String
Dim strNewValue As String
strNewValue = Replace(strOldValue, "eleph", "elephant")
strNewValue = Replace(strNewValue, "hippo", "hippotamus")
ReplaceValues = strNewValue
End Function
and call this in the query:
SELECT [AnimalID], [Habitat],
REPLACEVALUES([Animal]) AS [AnimalName]
FROM [Animals];
Ken Sheridan
Stafford, England
