Match records in two tables using SubString in SQL Server

1234

SOLVED CLOSED ACTIVE ANSWERED
User: 1234
Posted: on Feb 02, 2015 06:17 AM
Forum: SQL Server
Answer: 2
Views: 3233

table 1

Number

924-852132-200

927-856985-200

table 2

Digits

927-856985-100

927-12360-400

select count(Number)
from table 1 inner join table 2 on Number=digits

so if i enter 927-856985-200 it must match it on the digits table. but the digits column only has the last 3 digits of 100 and the number table has the last 3 digits of 200 however the ten digits infront of it is the same, so the expected output would be a count of 1

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on Feb 02, 2015 06:45 AM
Report

Please Refer Below Sql query

SQL

CREATE TABLE #temp(Number VARCHAR(15))
CREATE TABLE #temp1(Digits VARCHAR(15))

INSERT INTO #temp VALUES('924-852132-200')
INSERT INTO #temp VALUES('927-856985-200')
INSERT INTO #temp1 VALUES('927-856985-100')
INSERT INTO #temp1 VALUES('927-12360-400')

SELECT COUNT(*) [Count] FROM  #temp,#temp1 WHERE SUBSTRING(#temp.Number,0,12) = SUBSTRING(#temp1.Digits,0,12)
DROP TABLE #temp
DROP TABLE #temp1

OutPut

Count = 1

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Match records in two tables using SubString in SQL Server

Company

Explore

Follow us

Contact Us