All Forums Database
Claradata 8 posts Joined 05/12
13 Dec 2012
Sybase SIMILAR Function and Teradata 14.0 equivalent

We are working on a Sybase to Teradata Conversion project and ran into a situation where Sybase similar function is used in WHERE condition.
where similar(a.bank_nbr, b.bank_nbr) > 75
Example from Sybase Manual
The following statement returns the value 75:
SELECT SIMILAR( 'toast', 'coast' ) FROM iq_dummy. This signifies that the two values are 75% similar.
The function returns an integer between 0 and 100 representing the similarity between the two strings. The result can be interpreted as the percentage of characters matched between the two strings. A value of 100 indicates that the two strings are identical.
On Teradata 14.0, we have regexp_similar which does compare two strings, but it is either 0 or 1 (fail or pass)...But the business situation demands that the strings are matched and get a percent of characters matched. eg: My name can be John R Edwards Jr and my airline ticket says John Edwards.....which would be a 75% hit.
Do you have any suggestions on how we can implement this fuzzy logic in Teradata?
Appreciate the help.

dnoeth 4628 posts Joined 11/04
13 Dec 2012

I never tried it, but in TD14 you might do some calculation based on CHAR_LENGTH and EDITDISTANCE


Srichakra 15 posts Joined 11/11
14 Jul 2014

Hi all,
         Even I am also working on similar kind of project like migrating from Sybase to Teradata.While converting strored procs to teradata I came across few new functionalities in SYBASE.I did google in many places but no use,So finally posting the functionalities here as follows
please help me out
1)message STRING('CREATING TOP 25 TEMP_TOPSHIPPERS : ',now()) type info to client;
2)message ' ' to console;
3)set temporary option row_count = 25;
4)LOCATE (I think its like InString functionality please correct me if I am wrong)
Thanks in advance.

dnoeth 4628 posts Joined 11/04
15 Jul 2014

You should create a new topic for a new question.
I don't know Sybase, but:
1) + 2)
There's no capability like that in a Teradata SP, seems to be used to return some "work in progress" (or debugging) info to the calling client. You could only insert those strings into a temporary table and select from it after the SP finished.
setting the max number of rows to be updated/selected? Doesn't exist in a TD SP, you might use TOP or ROW_NUMBER in a Select instead.
Yes, INSTR can be used to replace LOCATE.


You must sign in to leave a comment.