SQL - replace returned data with other data

Question

I am retrieving data using the SQL syntax below:

SELECT TOP 5 EventId, EventTime, DeviceName, Comment, Tenant, TenantName, Individual, 
                InetDb.dbo.Individuals.FirstName, InetDb.dbo.Individuals.LastName, InetDb.dbo.IndivImages.UserImage
                FROM taclogdata.dbo.Event
                LEFT JOIN InetDb.dbo.Tenants
                    ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
                LEFT JOIN InetDb.dbo.Individuals
                    ON taclogdata.dbo.Event.Individual = InetDb.dbo.Individuals.IndivId 
                    AND taclogdata.dbo.Event.Tenant = InetDb.dbo.Individuals.TenantNdx
                LEFT JOIN InetDb.dbo.IndivImages
                    ON InetDb.dbo.Individuals.IndivId = InetDb.dbo.IndivImages.IndivNdx
                    AND InetDb.dbo.Individuals.TenantNdx = InetDb.dbo.IndivImages.TenantNdx
                WHERE (taclogdata.dbo.Event.EventTime  > DATEADD(hh, -3, GETDATE())AND taclogdata.dbo.Event.EventTime < GETDATE())
                    AND (taclogdata.dbo.Event.Comment='Reader entry' OR taclogdata.dbo.Event.Comment='Reader exit')
                    AND (taclogdata.dbo.Event.DeviceName = 'L9 1/4/1' 
                        OR taclogdata.dbo.Event.DeviceName='L1 2/1/1-2 MainD'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/1/3-4 MainD'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/6/1-2 Stair'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/2/1-2 FDT1')
                ORDER BY taclogdata.dbo.Event.EventTime DESC

This code works fine, however I'm trying to simplify the results.
I'm trying to simplify what the query returns, by replacing the DeviceName value from e.g. L1 2/1/3-4 MainD to Main Door when the results are shown (not replace the actual data in the database)

How may I achieve this please ?
Thanks in advance,
J

Answer 1

try this use replace function

SELECT TOP 5 EventId, EventTime, replace(DeviceName,'L1 2/1/3-4 MainD','L1 2/1/3-4 Main Door') as DeviceName, Comment, Tenant, TenantName, Individual, 
                InetDb.dbo.Individuals.FirstName, InetDb.dbo.Individuals.LastName, InetDb.dbo.IndivImages.UserImage
                FROM taclogdata.dbo.Event
                LEFT JOIN InetDb.dbo.Tenants
                    ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
                LEFT JOIN InetDb.dbo.Individuals
                    ON taclogdata.dbo.Event.Individual = InetDb.dbo.Individuals.IndivId 
                    AND taclogdata.dbo.Event.Tenant = InetDb.dbo.Individuals.TenantNdx
                LEFT JOIN InetDb.dbo.IndivImages
                    ON InetDb.dbo.Individuals.IndivId = InetDb.dbo.IndivImages.IndivNdx
                    AND InetDb.dbo.Individuals.TenantNdx = InetDb.dbo.IndivImages.TenantNdx
                WHERE (taclogdata.dbo.Event.EventTime  > DATEADD(hh, -3, GETDATE())AND taclogdata.dbo.Event.EventTime < GETDATE())
                    AND (taclogdata.dbo.Event.Comment='Reader entry' OR taclogdata.dbo.Event.Comment='Reader exit')
                    AND (taclogdata.dbo.Event.DeviceName = 'L9 1/4/1' 
                        OR taclogdata.dbo.Event.DeviceName='L1 2/1/1-2 MainD'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/1/3-4 MainD'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/6/1-2 Stair'
                        OR taclogdata.dbo.Event.DeviceName='L1 2/2/1-2 FDT1')
                ORDER BY taclogdata.dbo.Event.EventTime DESC

Answer 2

Here my suggestions:

If you can replace the data in your SELECT for example:

SELECT REPLACE(DeviceName,N'2/1/3-4 MainD',N'Main Door')

If you have many replacements, I would suggest to create a temporary table, join it and take the replacement from the temporary table.

Answer 3

The specific answer to your question is to use REPLACE() or a CASE statement. However, you should also change the WHERE clause to use IN and use table aliases so the code is easier to write and to read:

FROM taclogdata.dbo.Event e LEFT JOIN
     InetDb.dbo.Tenants t
     ON e.Tenant = t.TenantId LEFT JOIN
     InetDb.dbo.Individuals i
     ON e.Individual = i.IndivId AND e.Tenant = i.TenantNdx LEFT JOIN
     InetDb.dbo.IndivImages ii
     ON i.IndivId = ii.IndivNdx AND Ii.TenantNdx = ii.TenantNdx 
WHERE (e.EventTime  > DATEADD(hour, -3, GETDATE()) AND 
       e.EventTime < GETDATE()
      ) AND
      e.Comment IN ('Reader entry', 'Reader exit') AND
      e.DeviceName IN ('L9 1/4/1', 'L1 2/1/1-2 MainD', 'L1 2/1/3-4 MainD',
                       'L1 2/6/1-2 Stair', 'L1 2/2/1-2 FDT1'
                      )