I’m very new to SQL and am trying to create a view in SQL Server using 2 tables. There are some conditions will impact the way of selecting data and I would appreciate some guidance on what’s the logic to create a view for my requirements.
Condition: if the Description in Table1 is more than 10 characters and the character from the 9th to 11th are all numbers (3 numbers), then lookup in Table2 with the same Device_Name and Number. If there’s a match, select the Host_Name (T2) and replace the Device_Name in the view.
Example:
“Ethernet100” is more than 10 characters and there are all number from the 9th and 11th, use the number 100 and Device_name to lookup at Table2. Replace the Device_Name with Host_Name and put the Host_Name at the new column in the view [Host_On].
Table 1
Device_Name Desrciption Model --------------------------------------------- abc1 Ethernet100 sw1 abc2 Ethernet1/1/1 sw1 abc3 Ethernet sw1
Table 2
Device_Name Number Model Host_Name ----------------------------------------- abc1 100 sw2 efg
Desired result:
Device_Name Description Model Host_On ------------------------------------------------ efg Ethernet100 sw2 abc1 abc2 Ethernet1/1/1 sw1 [NULL] abc3 Ethernet sw1 [NULL]
Any advice or small part of code is appreciated.
Advertisement
Answer
SUBSTRING return empty string if string shorter than required.
TRY_CONVERT() return NULL if string isn’t numeric
SELECT COALESCE (t2.Host_Name, t1.Device_Name) as Final_Device_Name
, t1.[Description]
, COALESCE (t2.Model, t1.Model) as Final_Model
, t2.[Device_Name] as Host_On
, SUBSTRING(t1.[Description], 9, 3) result
FROM Table1 t1
LEFT JOIN Table2 t2
ON TRY_CONVERT( INT, SUBSTRING(t1.[Description], 9, 3)) = [Number];
OUTPUT
as you can see 1/1 doesnt match anything and get Host NULL
| Final_Device_Name | Description | Final_Model | Host_On | result | |-------------------|---------------|-------------|---------|--------| | efg | Ethernet100 | sw2 | abc1 | 100 | | abc2 | Ethernet1/1/1 | sw1 | (null) | 1/1 | | abc3 | Ethernet | sw1 | (null) | |