CODEWITHSUNDEEP
sqlsql-server
Wim Ombelets
Wim Ombelets

Reputation: 5265

join two tables on argument1 ends with argument2

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

Upvotes: 5

Views: 368

Answers (5)

Tim Lehner
Tim Lehner

Reputation: 15251

Something may not be as you believe, since most of the code on this page (including yours) should work, or at least return something.

I would recommend selecting these tables into local temp tables (allowing you to inspect data types/lengths), running ltrim(rtrim(...)) even if you think you don't need to, using N to mark literals as unicode, explicitly setting the width of your strings, etc. Anything that can increase simplicity or safety could help you find the problem.

Hair-pullers often end as head-slappers.

SELECT Name INTO #TABLE_A FROM [SERVER1].[dbo].[TABLE_A];

SELECT CompanyID INTO #TABLE_B FROM [SERVER1].[dbo].[TABLE_B];

-- Now you can check to see that the columns above are what you believe,
-- and then run your select or any of the selects that have been offered.

SELECT a.Name, b.CompanyID
FROM #TABLE_A AS a
    INNER JOIN #TABLE_B AS b
        ON LTRIM(RTRIM(a.Name)) LIKE N'%[_]' + CAST(b.CompanyID AS NVARCHAR(11))

Also, [SERVER1].[dbo].[TABLE_A] looks odd for 4-part naming. Do you mean [DB1].[dbo].[TABLE_A] or [SERVER1].[DB1].[dbo].[TABLE_A] instead?

Upvotes: 0

Amit Mittal
Amit Mittal

Reputation: 1127

Probably this might help:

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

I've tested your code and its working perfectly for me. The only reason that could be is that there are no matching records.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 2

bummi
bummi

Reputation: 27377

Like '%[_]' +  Cast(b.CompanyID as Varchar(100))

SQL-Fiddle

Upvotes: 1

Kenneth M. Nielsen
Kenneth M. Nielsen

Reputation: 487

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

Upvotes: 2

Related Questions