CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Looking_for_answers
Looking_for_answers

Reputation: 343

How to find a part of a string and do a join on that part of the string?

Thank you in advance.

I have a table1:

ID                       || Location   
ABC-999-8710-12345-000   || THYW.A
ABC-900-8560-54321-100   || UJNC.E
ABC-997-0090-98765-010   || QTHB.P
ABC-923-2340-67890-001   || OMYB.Y
ABC-009-8000-24680-000   || EVHO.N
ABC-009-8000-26954-010   || IUTO.N

I have another table2:

ID                       || Location   
ABC012345-00             || THYW.A
ABC054321-10             || UJNC.E
ABC098765-01             || QTHB.P
ABC067890-00             || OMYB.Y
ABC024680-00             || EVHO.N
ABC012678-01             || IUTO.N

I need the output table as:

ID                       || Location   || OutPut
ABC-999-8710-12345-000   || THYW.A     || Success
ABC-900-8560-54321-100   || UJNC.E     || Success
ABC-997-0090-98765-010   || QTHB.P     || Success
ABC-923-2340-67890-001   || OMYB.Y     || Success
ABC-009-8000-24680-000   || EVHO.N     || Success
ABC-009-8000-26954-010   || IUTO.N     ||

I need to take a part of the string and if it matches the ID in the table2 then i need to update the column output as Success.

This is the update query i have written:

update INT
set Output= 'Success'
FROM
[dbo].[table1] INT
join [dbo].[table2] SHB
on INT.ID=SHB.ID

Upvotes: 0

Views: 88

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can also simply just Use Left Join with Substring() Function as below :

SELECT T.ID,
       T.Location,
       CASE
           WHEN T1.Location IS NULL
           THEN ''
           ELSE 'Success'
       END AS 'Update'
FROM
(
    SELECT *
    FROM table1
) T
LEFT JOIN table2 T1 ON SUBSTRING(T1.ID, 5, 5) = SUBSTRING(T.ID, 14, 5);

Your Update Query will look like as below :

UPDATE INT SET INT.Output =  CASE
           WHEN SHB.Location IS NULL
           THEN ''
           ELSE 'Success'
       END 
FROM table1 INT
LEFT JOIN table2 SHB ON SUBSTRING(SHB.ID, 5, 5) = SUBSTRING(INT.ID, 14, 5);

Desired Result :

ID                       || Location   || OutPut
ABC-999-8710-12345-000   || THYW.A     || Success
ABC-900-8560-54321-100   || UJNC.E     || Success
ABC-997-0090-98765-010   || QTHB.P     || Success
ABC-923-2340-67890-001   || OMYB.Y     || Success
ABC-009-8000-24680-000   || EVHO.N     || Success
ABC-009-8000-26954-010   || IUTO.N     ||

Upvotes: 1

Valli
Valli

Reputation: 1450

use substring to extract the strings and join them.

 update INT
    set Output= 'Success'
    join [dbo].[table2] SHB
    on concat(substring(INT.ID,1,3), '0', substring(INT.ID,14,8))=SHB.ID

Upvotes: 2

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

You can simply use this query.

Assumption - The ID Pattern in table 1 remains same.

SELECT a.ID, b.Location, 
CASE WHEN b.Location IS NOT NULL 
    THEN 'Success' ELSE 'Failed' END as Output 
FROM table1 a 
   LEFT JOIN table2 b 
      ON LEFT(a.ID,3) + '0' + SUBSTRING(a.ID,14,5) + 
                  SUBSTRING(a.ID,19,3) = b.ID

If you want it for update query only

UPDATE a 
  SET Output = (CASE WHEN b.Location IS NOT NULL 
        THEN 'Success' ELSE 'Failed' END) 
            FROM table1 a 
       LEFT JOIN table2 b 
          ON LEFT(a.ID,3) + '0' + SUBSTRING(a.ID,14,5) + 
                      SUBSTRING(a.ID,19,3) = b.ID

Upvotes: 2

Related Questions