列1 列2
1 00001
1-1 00001.00001
1-1-1 00001.00001.00001
1-1-1-1 00001.00001.00001.00001
1-1-1-1-1 00001.00001.00001.00001
1-1-1-1-2 00001.00001.00001.00002
1-1-1-1-3 00001.00001.00001.00003
1-1-1-1-10 00001.00001.00001.00010求助,请教各位大佬 ,写一语句,可以通过列1数据生成列2的数据,谢谢!
1 00001
1-1 00001.00001
1-1-1 00001.00001.00001
1-1-1-1 00001.00001.00001.00001
1-1-1-1-1 00001.00001.00001.00001
1-1-1-1-2 00001.00001.00001.00002
1-1-1-1-3 00001.00001.00001.00003
1-1-1-1-10 00001.00001.00001.00010求助,请教各位大佬 ,写一语句,可以通过列1数据生成列2的数据,谢谢!
GO--测试数据
if not OBJECT_ID(N'tempdb..#test') is null
drop table #test
GOCREATE table #test(列1 nvarchar(100),列2 nvarchar(100))
insert #test
select '1','' union all
select '1-1','' union all
select '1-1-1','' union all
select '1-1-1-1','' union all
select '1-1-1-1-1','' union all
select '1-1-1-1-2','' union all
select '1-1-1-1-3','' union all
select '1-1-1-1-10','' select * from #testdrop table #test填充的函数:CREATE FUNCTION [dbo].[fn_GetSuiteNum](@num VARCHAR(5))
RETURNS VARCHAR(5)
AS
BEGIN
IF(len(@num)=1)
BEGIN
SET @num='0000'+@num
END
ELSE IF(len(@num)=2)
BEGIN
SET @num='000'+@num
END
ELSE IF(len(@num)=3)
BEGIN
SET @num='00'+@num
END
ELSE IF(len(@num)=4)
BEGIN
SET @num='0'+@num
END RETURN @num
END
我自己想的是先取出‘-’在字符串出现的次数,然后通过游标每行去拆分,通过上面的自定义函数把拆分出来的每部分填充。
最后用update #test set 列2 = replace(列1,'-','.')。
不知道大佬们有什么好的想法没?
GO
if OBJECT_ID(N'tempdb..t') is not null
drop table t
GO
CREATE table t(c1 varchar(100))
insert t
select '1' union all
select '1-1' union all
select '1-1-1' union all
select '1-1-1-1' union all
select '1-1-1-1-1' union all
select '1-1-1-1-2' union all
select '1-1-1-1-3' union all
select '1-1-1-1-10'
----- 以上为测试表及测试数据 -----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 ([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
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
----- 增加字符串分割表值函数--查询
select *
,stuff((select '.'+right('0000'+item,5) from [dbo].[Fun_String2ToStringArray](c1,'-') as f for xml path('')),1,1,'') as result
from t