Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How To Identify Records From Two Different SQL Tables

Posted on 2016-08-05
Medium Priority
42 Views
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
Question by:Member_2_7970395
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 30

Accepted Solution

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

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

## Featured Post

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month7 days, 18 hours left to enroll