Oracle's equivalent TRANSLATE command

[Guest]

Hello
Does anyone know whether there is a SQL command in Access that is equivalent to Oracle's TRANSLATE command? What is the command and the parameters

Thanks
jp

 

[Michel Walsh]

Hi,


I would rather use an inner join.


Table1 ' table
Id, Description ' fields
0 zero
1 one
2 two
3 three ' data





SELECT a.*. Table1.Description
FROM a INNER OIN table1 on a.id = table1.id





The advantage is that anyone can add data, or change it, in table1,
while with constants written in the SQL code requires, for even the most
basic maintenance, an access to the SQL code.... and if everyone has to
modify your SQL code, who is responsible for it, at the end? No, data in
table, not in code, is definitively better for everyone, imho, and using
lists of constants in code is to be avoided.... But if you still prefer
having those constants in your code:


SELECT CHOOSE( a.id, "one", "two", "three", "four" ) As
ValueFromOneToFour,
SWITCH( condition1, value1, condition2, value2, condition3, value3,
true, defaultValue) As LikeACase




with SWITCH behaving like a CASE statement, and CHOOSE selection the
argument accordingly to the integer value of its first argument.



Hoping it may help,
Vanderghast, Access MVP

Hello,
Does anyone know whether there is a SQL command in Access that is

equivalent to Oracle's TRANSLATE command? What is the command and the
parameters?

 

[Guest]

But this does not accomplish what I am trying to do with TRANSLATE. I have a table with hundreds of thousands of text strings. I want to use the TRANSLATE command to convert the non-alphabetic characters to a space and then trim the text so I can group the alphabetic text strings

j

----- Michel Walsh wrote: ----

Hi


I would rather use an inner join


Table1 ' tabl
Id, Description ' field
0 zer
1 on
2 tw
3 three ' dat





SELECT a.*. Table1.Descriptio
FROM a INNER OIN table1 on a.id = table1.i





The advantage is that anyone can add data, or change it, in table1
while with constants written in the SQL code requires, for even the mos
basic maintenance, an access to the SQL code.... and if everyone has t
modify your SQL code, who is responsible for it, at the end? No, data i
table, not in code, is definitively better for everyone, imho, and usin
lists of constants in code is to be avoided.... But if you still prefe
having those constants in your code


SELECT CHOOSE( a.id, "one", "two", "three", "four" ) A
ValueFromOneToFour
SWITCH( condition1, value1, condition2, value2, condition3, value3
true, defaultValue) As LikeACas




with SWITCH behaving like a CASE statement, and CHOOSE selection th
argument accordingly to the integer value of its first argument



Hoping it may help
Vanderghast, Access MV

Hello
Does anyone know whether there is a SQL command in Access that i

equivalent to Oracle's TRANSLATE command? What is the command and th
parameters

 

[Michel Walsh]

Hi,


I would write a VBA procedure that would loop through all the
element of the string and use Mid() to replace the non-alphanumeric
characters by a space:


Public Function Translate(ByVal MyString As String) As String
Dim i As Long
Dim b As String

Translate = MyString

For i = 1 To Len(MyString)
b = UCase(Mid(MyString, i, 1))
If b < "A" Or b > "Z" Then
Mid(Translate, i, 1) = " "
End If
Next i

End Function



Note that this function assumes you do not deal with accent (any letter
outside a-z A-Z, would be replace by a space).



Hoping it may help,
Vanderghast, Access MVP

But this does not accomplish what I am trying to do with TRANSLATE. I

have a table with hundreds of thousands of text strings. I want to use the
TRANSLATE command to convert the non-alphabetic characters to a space and
then trim the text so I can group the alphabetic text strings.