enter image description here

Join 2 tables by using id in the first table and DB name in the second table with the matching string and update the name in the second tables.

Please help me if it is possible.



This is what I came up with:

set nocount on
Declare @Table1 table (ID int, Name varchar(100))
Declare @Table2 table ([DB_Name] varchar(100), Name varchar(100))

insert into @Table1 (ID, Name) values

insert into @Table2([DB_Name],Name) values

update tb2
set tb2.Name = tb1.Name
from @table1 tb1 join
@table2 tb2 on
tb2.[DB_Name] like '%' + convert(varchar(10),tb1.Id) + '%'

select * from @Table2
  • I think you assumed that the number in id can only appear in the style _ 1111 _ in dbname - what if there are dates with century in db_name? A more robust formula I think is PATINDEX('%[ _ ]' + CONVERT(varchar(10), tb1.Id) + '[ _ ]%', tb2.DB_Name) > 0. The symbol _ should appear without spaces either side. But when I type " _ 1111 _ " it comes out "1111", italics. – Robert Carnegie Jun 19 '17 at 16:39

Looking at the table data I assume [DB_Name] field has four elements, and element #3 is always the User Id? With that assumption in mind, the below code should help you :-

update t2
SET t2.Name = t1.Name
from @table1 t1
join @table2 t2
    on t1.ID = PARSENAME(REPLACE(t2.[DB_Name], '_', '.'), 2) 

The trick I have used here is to use PARSENAME function which is used by SQL Server code to derive object name elements from a full object name, e.g. ServerName.DatabaseName.Schema.ObjectName

To return element#3 from DB_Name, we would need to specify value 2 for second argument for PARSENAME to work correctly.

Full test code below :-

declare @table1 table
ID     int,
Name   varchar(30)

INSERT @table1 
    ( 1112, 'john'),

select * from @table1 

declare @table2 table
[DB_Name]   varchar(255),
Name       varchar(30)
INSERT @table2
    ( 'TS_aa_1112_12Jun17' , null),
    ('Cs_bbb_1114_15Aug16', null),
    ( 'Tsts_cc_1113_19dec16' , null),
    ( 'tt_ddss_1118_12Jan17', null)

select * from @table2    


-- test calcId
SELECT t2.*, PARSENAME(REPLACE(t2.[DB_Name], '_', '.'), 2) as CalcId
from @table2 t2

-- Apply update to name column
update t2
SET t2.Name = t1.Name
from @table1 t1
join @table2 t2
    on t1.ID = PARSENAME(REPLACE(t2.[DB_Name], '_', '.'), 2) 

-- Show final results
select * from @table2 t2
  • I should add some disclaimers here... This "hack" works ok as DB_Name has four assumed elements. A custom UDF would be best to handle custom field splitting. :-) – Terry C Jun 19 '17 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.