截取某个字段的部分字符到另一个字段解决办法_Sql Server

截取某个字段的部分字符到另一个字段
字段details里面有大量的信息，如：164CM,50KG,34D
新建一个字段tall用来专门保存身高信息
如何吧details里面，符合1？？CM这样的信息，都复制到tall字段？

------解决方案--------------------

SQL code

Select info into #TBfrom(select '164CM,50KG,34D' as infounion allselect '175CM,62KG,35B' as infounion allselect '201CM,89KG,37C')Tselect 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    info164CM    164CM,50KG,34D175CM    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 tbgocreate table tb( id int identity(1,1), details varchar(20), tall varchar(10))goinsert into tb(details,tall)select '164CM,50KG,34D',null union allselect '165CM,50KG,34D',null union allselect '166CM,50KG,34D',nullgoupdate tb set tall=substring(details,1,charindex(',',details)-1)select * from tb/*id          details              tall----------- -------------------- ----------1           164CM,50KG,34D       164CM2           165CM,50KG,34D       165CM3           166CM,50KG,34D       166CM(3 行受影响)*/