把一列变成多列

[flyfly2008]

如何把一列 37.5*28.2*26.8cm
分解成三列 37.5 28.2 26.8
求大神帮助

[jimson0904]

写一个涵数，字符串分割的。上百度搜一下就有

[唐诗三百首]

create table #t(col varchar(100))

insert into #t(col)
 select '37.5*28.2*26.8cm'


select col1=[1],col2=[2],col3=[3]
 from (select rn=row_number() over(order by getdate()), 
              val=replace(o.value('.','varchar(100)'),'cm','')
        from (select val=convert(xml,'<r><n>'+replace(col,'*','</n><n>')+'</n></r>') 
              from #t) t
        cross apply val.nodes('/r/n') x(o)) t
 pivot(max(val) for rn in([1],[2],[3])) u

/*
col1            col2            col3
--------------- --------------- ---------------
37.5            28.2            26.8

(1 row(s) affected)
*/

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	v NVARCHAR(20)	
)
SET NOCOUNT ON
INSERT INTO t VALUES ('37.5*28.2*26.8cm')
INSERT INTO t VALUES ('48.8*18.8*2.1cm')
GO
-------- 以上为测试表及测试数据 -----------

--方法1：repalce + ParseName
;WITH cte AS(
	SELECT 
		v
		,replace(replace(replace(v,'.','^'),'*','.'),'cm','') AS v2 
	FROM t
)
SELECT v
,replace(PARSENAME(v2,3),'^','.') AS c1
,replace(PARSENAME(v2,2),'^','.') AS c2
,replace(PARSENAME(v2,1),'^','.') AS c3
FROM cte
/*
v                    c1     c2    c3
-------------------- ------ ----- ----
37.5*28.2*26.8cm     37.5   28.2  26.8
48.8*18.8*2.1cm      48.8   18.8  2.1
*/

--方法2: 分割函数
--2.1 增加一个分割字符串的表值函数
IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL 
	DROP FUNCTION [dbo].[Fun_String2ToStringArray]
GO
CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE (rid INT IDENTITY(1,1) PRIMARY KEY,[item] NVARCHAR(max))
AS
BEGIN
    IF LEN(@split) = 0
      BEGIN
        SET @split = N','
      END
 
    DECLARE @xml XML;
    SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
 
    INSERT INTO @table(item)
      SELECT item
      FROM   (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
              FROM   @xml.nodes('/x') t(c)) t
      WHERE  item IS NOT NULL
 
    RETURN
END
GO

--2.2 查询
;WITH cte AS (
	SELECT * FROM t CROSS APPLY [dbo].[Fun_String2ToStringArray](replace(v,'cm',''),'*') AS f
)
SELECT v
	,tt.[1] AS [c1] 
	,tt.[2] AS [c2]
	,tt.[3] AS [c3]
FROM cte PIVOT (MAX(item) FOR rid IN([1],[2],[3])) AS tt

/*
v                    c1     c2    c3
-------------------- ------ ----- ----
37.5*28.2*26.8cm     37.5   28.2  26.8
48.8*18.8*2.1cm      48.8   18.8  2.1
*/