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 sw1abc2 Ethernet1/1/1 sw1abc3 Ethernet sw1Table 2
Device_Name Number Model Host_Name-----------------------------------------abc1 100 sw2 efg Desired result:
Device_Name Description Model Host_On------------------------------------------------efg Ethernet100 sw2 abc1abc2 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 t1LEFT 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) | |