All Forums Database
amerbaig 7 posts Joined 07/11
12 Mar 2014
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.

 

 

Raja_KT 1246 posts Joined 07/09
12 Mar 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.