join two tables on argument1 ends with argument2

Question

TABLE_A contains an NVARCHAR(200) NOT NULL column Name. Its fields are made up of company names followed by an underscore and ending in a company id, e.g.:

MYCOMPANY_102

TABLE_B's PK is an INT NOT NULL column called CompanyID and would contain e.g. 102

My query, to inner join these two tables where one argument ends with the other, yields no results. In case anyone wonders the obvious: yes, there are matching records.

SELECT * FROM [SERVER1].[DB1].[dbo].[TABLE_A] a
INNER JOIN [SERVER2].[DB2].[dbo].[TABLE_B] b
ON a.Name LIKE '%[_]' + CONVERT(NVARCHAR, b.CompanyID)

Any corrections and insight into why my query can not work, is most welcome.

EDIT: company names can contain digits and/or underscores anywhere, I'm ONLY looking to lock on to a possibly last occurrence of an underscore, followed by an arbitrary natural int. Could be _1, could be _205952 for all I know. There are NO leading zeros in the digits suffix

Answer 1

What you have to do is take that ID out from the name and join on it like here.

SELECT * FROM [SERVER1].[dbo].[TABLE_A] a
INNER JOIN [SERVER2].[dbo].[TABLE_B] b
cast(right(a.NAME, len(a.NAME)-charindex('_',A.NAME)) as int) = b.CompanyID

Answer 2

It is possible that your problem is trailing blanks on the company name. The documentation points out:

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the ISO standard. ASCII LIKE is compatible with earlier versions of SQL Server.

This is just a possibility. As a note, when you use varchar or nvarchar in a convert(), you should always include the length. This is not affecting you here.

I think you are better off with this comparison:

cast(SUBSTRING(a.name, charindex('_', a.name)+1, LEN(a.name)) as int) = b.CompanyId

By using an equi-join on CompanyId, you have the opportunity to use indexes on that column.