Simple answer: don't.
SQL is no good at string processing. Rather than trying to extract a value from a string with a load of values stuffed in it, extract the original value directly.
SELECT DISTINCT
Autoid,
TimeSlotId,
STUFF((
SELECT DISTINCT ', ' + CAST(CONVERT(varchar, mtt.TimeFrom, 0) AS nvarchar(200)) + ' - ' + CAST(CONVERT(varchar, mtt.TimeTo, 0) AS nvarchar(200))
FROM MST_TBL_BookingConferenceRoom EQ
LEFT OUTER JOIN MST_TBL_TimeSlot mtt ON ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%'
WHERE ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%'
FOR XML PATH('')
), 1, 2, '') AS TimeBooked,
(
SELECT MAX(CASE WHEN mtt.TimeFrom > mtt.TimeTo THEN mtt.TimeFrom ELSE mtt.TimeTo END)
FROM MST_TBL_BookingConferenceRoom EQ
LEFT OUTER JOIN MST_TBL_TimeSlot mtt ON ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%'
WHERE ',' + mtb.TimeSlotId + ',' LIKE '%,' + cast(mtt.Autoid AS nvarchar(200)) + ',%'
) As MaxTime
FROM
MST_TBL_BookingConferenceRoom mtb
LEFT JOIN PPMS_TBL_EmployeeMaster AS Em ON mtb.EmployeeID = em.Autoid
LEFT JOIN PPMS_TBL_Designation Des ON em.DesignationId = Des.DesignationId
WHERE
1 = 1
ORDER BY
Autoid DESC
;
Of course, this assumes your
TimeFrom
and
TimeTo
columns are using a proper data type; if you've stored them as strings, then you won't get the expected results, since
'10'
is less than
'9'
using string comparison.
Also, your joins suggest that you're storing multiple values in a single field, which is almost never a good idea. As you can probably see now from the complexity of the query required to join your tables. It would be better to redesign your database to normalize the data.