How to TRANSLATE unicode to latin in Teradata?

Pasha picture Pasha · Aug 5, 2015 · Viewed 9.2k times · Source

My table contains a field lastName that is UNICODE

I need to implement the SOUNDEX function, but it won't work because it requires only latin. So I tried converting it to latin but still get the same error only latin letters allowed

Here is what i tried

SEL * 
FROM tab_test
WHERE SOUNDEX(REGEXP_REPLACE(lastName, '[^A-Z]', '')) = 'smith' 



SEL * 
FROM tab_test
WHERE SOUNDEX(TRANSLATE(lastNameUSING unicode_to_latin)) = 'smith' 

How can I correct the issue


EDIT

Here is what i tried as dnoeth suggests. But it still does not work

     SEL * 
     FROM tab_test
     WHERE SOUNDEX(TRANSLATE(REGEXP_REPLACE(
     lastName,'[^a-zA-Z]','') USING   
     UNICODE_TO_LATIN))  = 's530' 
     

EDIT 2

Here is the query that does not work

 SEL  lastName, REGEXP_REPLACE(lastName, '[^a-zA-Z]', '') lastName_regex 
 FROM (SEL * 
 FROM tab_test
 WHERE personId < 10 
 ) der 
 WHERE SOUNDEX(REGEXP_REPLACE(lastName, '[^a-zA-Z]', '')) = 's530'  

The result of the REGEXP sub query is the following

SEL  lastName, REGEXP_REPLACE(lastName, '[^a-zA-Z]', '') lastName_regex 
 FROM (SEL * 
 FROM tab_test
 WHERE personId < 10 
 ) der  

Result is copied directly from teradata sql assistant

    LASTNAME    lastName_regex
1   Smith                   Smith              
2   Smith                   Smith              
3   Smith                   Smith              
4   Smith                   Smith              
5   Smith                   Smith              
6   Smith                   Smith              
7   Smith                   Smith              
8   Smith                   Smith              

Answer

dnoeth picture dnoeth · Aug 5, 2015

Your regex removes lowercase a to z, too.

So try either

REGEXP_REPLACE(lastName, '[^a-zA-Z]', '') -- explicitly add lowercase

or

REGEXP_REPLACE(lastName, '[^A-Z]', '',1,0,'i')) -- do a case insensitive comparison

Btw, the result of a SOUNDEX('smith') is not 'smith', but 's530'.