MSSQL怎样将该类字段分割出来
MSSQL怎样将此类字段分割出来？
有一个表A:
A01[厚度] A02[宽度] A03[长度] A04[规格]
null null null 8*150*990
....
将A04里的三个数字分割出来分别放到A01,A02,A03字段里，结果如下：
A01[厚度] A02[宽度] A03[长度] A04[规格]
8 150 990 8*150*990
....
哪种方法最高效最好？
------解决方案--------------------
------解决方案--------------------
有一个表A:
A01[厚度] A02[宽度] A03[长度] A04[规格]
null null null 8*150*990
....
将A04里的三个数字分割出来分别放到A01,A02,A03字段里，结果如下：
A01[厚度] A02[宽度] A03[长度] A04[规格]
8 150 990 8*150*990
....
哪种方法最高效最好？
------解决方案--------------------
select
parsename(replace(A04,'*,'.'),3) as 'A01',
parsename(replace(A04,'*,'.'),2) as 'A02',
parsename(replace(A04,'*,'.'),1) as 'A03',
A04
from
tb
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-03-31 16:25:53
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A01] sql_variant,[A02] sql_variant,[A03] sql_variant,[A04] varchar(9))
insert [tb]
select null,null,null,'8*150*990'
--------------开始查询--------------------------
select
parsename(replace(A04,'*','.'),3) as 'A01',
parsename(replace(A04,'*','.'),2) as 'A02',
parsename(replace(A04,'*','.'),1) as 'A03',
A04
from
tb
----------------结果----------------------------
/* A01 A02 A03 A04
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------
8 150 990 8*150*990