Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How To Identify Records From Two Different SQL Tables

Posted on 2016-08-05
2
Medium Priority
?
44 Views
Last Modified: 2016-08-05
I need to identify records (BY THEIR FIRST SIX (6)) digits between two tables.  I using the below SQL statement:

SELECT TABLE2.COMPANY_ID AS ACCOUNTS_WITH_A_STATUS, TABLE1.ACCOUNT AS MATCHING_ACCOUNTS
FROM TABLE1
JOIN TABLE2 ON TABLE1.ACCOUNT = TABLE1.ACCOUNT
WHERE TABLE2.STATUS = 'A' AND TABLE2.COMPANY_ID + '%%%%%%' LIKE '%%%%%%' + TABLE1.ACCOUNT + '%%%%%%'

At first, this query seemed to work, however, it seems to be matching the LAST six digits, instead of the FIRST six digits.  Please see attachment.  Any assistance with this issue would be greatly appreciated.
Sample_1.PNG
0
Comment
Question by:Member_2_7970395
2 Comments
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 2000 total points
ID: 41744910
% is the joker any number of any characters. You want to use _ for any single character or [0-9] for a digit, so the LIKE pattern for 6 digits is '[0-9][0-9][0-9][0-9][0-9][0-9]'.

If you want the first 6 character to match, keep it simple, use LEFT(table1.column,6) = LEFT(table2.column,6).

col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' will match any 6 digit number, but you want them to be same, not any number, don't you?

Bye, Olaf.
0
 

Author Comment

by:Member_2_7970395
ID: 41744967
It worked perfectly!!  Thank you very much!!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question