replace some value in sql with Null and perform count function in sql server while performing group by

there is LAT_LONG field which I want to count for each ID, the LAT_LONG contain some string value as ‘null/null’ which I want to replace with NULL, and than perform the group by operation

 select ID, COUNT(LAT_LONG) as LAT_LONG_CAT from mytable group by ID

How to replace ‘null/null’ string with Null in LAT_LONG field

>Solution :

Use a CASE expression:

SELECT ID,
       COUNT(CASE WHEN LAT_LONG <> 'null/null'
                  THEN LAT_LONG END) AS LAT_LONG_CNT
FROM mytable
GROUP BY ID;

The above CASE expression will treat a value of null/null as NULL, otherwise it counts the LAT_LONG field directly.