SQL Query Help

[glenimp617]

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]

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.

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

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.

ON SUBSTRING(l.asset, 3, 6)

 

[glenimp617]

Thanks, I'll try those suggestions tomorrow

 

[HungryHippos]

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]

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]

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

 

[Rob_B]

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]

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]

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.