Select info 
into #TB
from(
select '164CM,50KG,34D' as info
union all
select '175CM,62KG,35B' as info
union all
select '201CM,89KG,37C')T


select substring(info,patindex('%CM%',info) - 3,patindex('%CM%',info)+1) as leng,info 
from #TB 
where info like '1[1-9][1-9]CM%'

/*
leng    info
164CM    164CM,50KG,34D
175CM    175CM,62KG,35B
*/

drop table #TB

------解决方案--------------------
这个方法是最好的，建一个函数做一下拆分,不知道这样行不行。

CREATE TABLE DEMO (ID BIGINT IDENTITY(1,1),DETAILS CHAR(800),TALL CHAR(800))

INSERT INTO DEMO
SELECT '164CM,50KG,34D',''
UNION ALL
SELECT '175CM,62KG,35B',''
UNION ALL
SELECT '201CM,89KG,37C',''

UPDATE DEMO SET TALL = DBO.FUN_SPLIT(DETAILS) WHERE DBO.FUN_SPLIT(DETAILS)<>''

GO
CREATE FUNCTION FUN_SPLIT(
	@STR CHAR(8000)=''
)
RETURNS CHAR(8000)
AS
BEGIN
	DECLARE @RE CHAR(8000)
	SELECT @RE = ''
	
	WHILE @@ROWCOUNT>0
	BEGIN
		SELECT @RE=
			CASE WHEN SUBSTRING(@STR,0,CHARINDEX(',',@STR)) LIKE '%CM'  
				THEN SUBSTRING(@STR,0,CHARINDEX(',',@STR))
			ELSE
				@RE
			END,
			@STR = SUBSTRING(@STR,CHARINDEX(',',@STR)+1,LEN(@STR))
		WHERE CHARINDEX(',',@STR)>0
	END	
	RETURN @RE	
END


------解决方案--------------------
SQL code

--格式如果固定这么用
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 id int identity(1,1),
 details varchar(20),
 tall varchar(10)
)
go
insert into tb(details,tall)
select '164CM,50KG,34D',null union all
select '165CM,50KG,34D',null union all
select '166CM,50KG,34D',null
go
update tb set tall=substring(details,1,charindex(',',details)-1)
select * from tb
/*
id          details              tall
----------- -------------------- ----------
1           164CM,50KG,34D       164CM
2           165CM,50KG,34D       165CM
3           166CM,50KG,34D       166CM

(3 行受影响)
*/