如何对包含单词和数字的SQL服务器中的VARCHAR列进行排序?

I have a varchar(100) field that contains both letters and numbers. The values are usually in the form of car 1, car 10, car 100, car 20. But the values can have any word preceding the number. Is there any way to sort these values numerically so that car 2 will come before car 10? Thanks.I have a varchar(100) field that contains both