1

Consider two sql-server table columns

Column1                Column2
mr white james bond    james cameron  
gordon ramsay          Harry Gordon Selfridge

I need to select these tables are equal by comparing 5 letters match in name. Example

  • Name mr white james bond and james cameron contains common five letter word james.
  • Name gordon ramsay and Harry Gordon Selfridge contains common five letter word gordo.

Is it possible ?. I can match first 5 characters using this

 SELECT * FROM table1 A
 INNER JOIN table2 B ON UPPER(SUBSTRING(A.name,1,5))=UPPER(SUBSTRING(B.name,1,5))

But can't match all 5 characters possibilities.

Mani7TAM
  • 469
  • 3
  • 10
  • read out the article : http://stackoverflow.com/questions/28636051/how-to-join-two-tables-based-on-substring-values-of-fields – Happy Coding Dec 01 '16 at 07:51
  • @HappyCoding Thanks for your answer. But, I don't have any predefined values of substring START and END - as your reference article link. – Mani7TAM Dec 01 '16 at 07:58
  • You could use `A.name like '%'+substring(B.name,1,5)+'%'` to solve the substring part in A. Not sure how to deal with B having the substring anywhere though. – Mr Lister Dec 01 '16 at 07:59
  • @MrLister Thanks. In my case, the substring start,end values not always (1,5). It should be dynamic based on possibilities of 5 character in name string. Example: `mr white james bond ` will be in substring like `mr whi` , `r whit`, `white`, `hite j`...`james`,etc... and matches the `james`. – Mani7TAM Dec 01 '16 at 08:08

1 Answers1

1

Try concatenating both the strings.

SELECT A.name, B.name FROM table1 A
INNER JOIN table2 B ON '%' + UPPER(SUBSTRING(A.name,1,5)) + '%' LIKE '%' + UPPER(SUBSTRING(B.name,1,5)) + '%'
ORDER BY A.id
Pradeep Kumar
  • 4,065
  • 2
  • 33
  • 40