select mid(room_number_shown,1,Instr(room_number_shown,"号")-1) from test1

------解决方案--------------------
实测成功：
SQL code

SELECT mid(room_number_show, 1, INSTR(room_number_show, "号") - 1
FROM 表名;