Sybase SIMILAR Function and Teradata 14.0 equivalent

Database
Enthusiast

Sybase SIMILAR Function and Teradata 14.0 equivalent

Folks,

We are working on a Sybase to Teradata Conversion project and ran into a situation where Sybase similar function is used in WHERE condition.

eg:

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.

Usage

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.

Thanks

3 REPLIES
Senior Apprentice

Re: Sybase SIMILAR Function and Teradata 14.0 equivalent

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

Dieter

Enthusiast

Re: Sybase SIMILAR Function and Teradata 14.0 equivalent

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.

Senior Apprentice

Re: Sybase SIMILAR Function and Teradata 14.0 equivalent

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.

3)

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.

4)

Yes, INSTR can be used to replace LOCATE.