get the first value in a csv field from sql server

You can easily fix your logic by adding a comma to the field for the call to charindex():

select left(t.FIELD, charindex(',', t.FIELD + ',') - 1) as first_value 

Note that I also replaced substr() with left() because that is more concise.