It turns out this is a quirk of SQL SERVER’s implementation of SQL.
It also turns out in all my years working with databases I must have been cleansing my data enough to never encounter this (in this instance I was generating SQL statements in Excel and was explicitly told not to touch import values [some of which included an incorrect trailing space]).