SQL Query Help

glenimp617 · 23 Aug 2022 at 16:20

Hi Everyone,

SQLServer1 - Database Assets (column assets has a record called as123456)
SQLServer2 - Database SCCM (column name has a record called pc123456)

Query

SELECT l.Asset AS [Asset], m.name AS [Name] FROM sqlserver1.assets.dbo.assets l
LEFT JOIN sqlserver2.sccm.dbo.computers m
ON replace(l.asset,'as','') LIKE '%m.name%'

I'm trying to remove the 'as' part of the asset name and then match it up with the pc record in the second database.
The value of the name column keeps coming up as null

any ideas?

t31os · 23 Aug 2022 at 17:19

I think your LIKE clause is probably being treated as literal, so it's looking for m.name (with or without characters either side) and not the values from the column.

Try one of these.

SQL:

LIKE '%' + m.name + '%'

SQL:

LIKE CONCAT('%',m.name,'%')

It might also be faster (you'll have to test if that's the case) to substring the asset column instead of doing the replace.

SQL:

ON SUBSTRING(l.asset, 3, 6)

glenimp617 · 23 Aug 2022 at 17:27

Thanks, I'll try those suggestions tomorrow

HungryHippos · 23 Aug 2022 at 22:34

I don't think I've done a join on a LIKE before, but instead of removing 'as' and doing a LIKE, perhaps you could replace 'as' with 'pc' so it matches the name in the other database, then join on that?

Not got SQL to hand at the moment but this might work as a slight mod to your original query:

SELECT
l.Asset AS [Asset],
m.name AS [Name]
FROM sqlserver1.assets.dbo.assets l
LEFT JOIN sqlserver2.sccm.dbo.computers m
ON replace(l.asset,'as','pc') = m.name

radderfire · 23 Aug 2022 at 22:49

On the last line, should:

l.asset be l.Asset

i.e. you've got a case mismatch.

I did also wonder whether on the last line:

l.asset should be l.assets

but I think it's probably ok.

HungryHippos · 23 Aug 2022 at 22:51

SQL isn't always case sensitive, depends how it's configured.

Rob_B · 23 Aug 2022 at 23:00

You're also doing a left join not inner so that's why some names could come up as null.
Also another vote for substr.

glenimp617 · 24 Aug 2022 at 08:19

Thanks everyone,

Unfortunately the second table (pc123456 doesn't always start pc sometimes it's lt). I'm going to use a combination of substring and adding in the + this morning

Rob_B · 24 Aug 2022 at 08:32

If you only ever want to keep 0-9 then just compress out any other character? Unless it's just the first 2 you need rid of in which case just use substr.