Using new columns created from PARSENAME

February 9, 2022

I have split the column PointName using PARSENAME and created 2 new columns and I am needing now to use a WHERE clause to only pull the data for those 6 types. Is there a way to do this?

SELECT *, reverse(PARSENAME(REPLACE(REVERSE(pointname),'.','.'),2))as [Type] , reverse(PARSENAME(REPLACE(REVERSE(pointname),'.','.'),3))as [Point] 
from RawAnalog RA
Where ra.PointName LIKE '%SKYLINE%'
      and ra.Point IN ('MaTmp', 'OaTmp', 'SaTmp', 'ChwVlv','SaStp', 'SaCFM')

>Solution :

Filling in the blanks a bit, but I would guess you could move the PARSENAME to the FROM and then you can filter more easily in the WHERE:

SELECT {Columns you need},
       V.[Type],
       V.Point
FROM dbo.RawAnalog RA
     CROSS APPLY(VALUES(REVERSE(PARSENAME(REPLACE(REVERSE(pointname), '.', '.'), 2)),REVERSE(PARSENAME(REPLACE(REVERSE(pointname), '.', '.'), 3))))V([Type],Point)
WHERE RA.PointName LIKE '%SKYLINE%'
  AND RA.Point IN ('MaTmp', 'OaTmp', 'SaTmp', 'ChwVlv', 'SaStp', 'SaCFM')
  AND V.[Type] IN ({List of acceptable values});