Help needed with more specific prefix matching (closest match)

Database
Enthusiast

Help needed with more specific prefix matching (closest match)

Hi,

Table1 contains following entries

Code, Name

===========================

1,USA 

1403,Alberta/Canada

1809,Dominican Republic

180979555,Dominican Republic-Santa-Dom

Table2 contains

180979555123456

1

140

1809

1616

Is it possible to get following output in SQL?

180979555123456, Dominican Republic-Santa-Dom

1, USA

140, Alberta/Canada

1809, Dominican Republic

1616, USA

Is closest matching in possible in TD SQL?

Thanks a lot.


1 REPLY
Enthusiast

Re: Help needed with more specific prefix matching (closest match)

There should be a fixed formulated logic to arrive at your required output. If not so, then a lot of formulated logics (permutation and combination can arise).

How do you arrive at 1616, USA? Many questions can arise like is it possible  to have  161, USA- 116,USA-16161,USA and so on and so forth.

If the logic is there, then you can use

case when,substring, like etc

etc.

Cheers,