22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(37))
Insert #T
select N'1.25.26.27.28' union all
select N'1.29.32.35' union all
select N'57.58.59.60.62.63' union all
select N'57.65.66.67.68'
Go
--测试数据结束
DECLARE @k INT = (SELECT MAX(len(col)-len(replace(col,'.',''))) FROM #T)
DECLARE @str NVARCHAR(MAX)=''
SELECT @str=@str+' union select '+dbo.f_getcol(col,'.',@k+1) FROM #T
SET @str = STUFF(@str,1,8,'')
EXEC(@str)
CREATE FUNCTION dbo.f_getcol
(
@s VARCHAR(8000) , --待分拆的字符串
@split VARCHAR(10), --数据分隔符
@k INT --最大长度
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @str NVARCHAR(MAX)=''
DECLARE @splitlen INT
DECLARE @i INT = 1
DECLARE @k1 INT = 0
SET @splitlen = LEN(@split + 'a') - 2
WHILE @k > 0
BEGIN
IF CHARINDEX(@split, @s)>0
BEGIN
SET @str = @str+','+LEFT(@s, CHARINDEX(@split, @s) - 1)+' as ['+RTRIM(@i)+']'
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
END
ELSE
BEGIN
IF @k1=0
BEGIN
SET @str = @str+','+@s+' as ['+RTRIM(@i)+']'
SET @k1=1
END
ELSE
BEGIN
SET @str = @str+',null as ['+RTRIM(@i)+']'
END
END
SET @i = @i +1
SET @k = @k-1
END
SET @str = STUFF(@str,1,1,'')
RETURN @str
END
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(37))
Insert #T
select N'1.25.26.27.28' union all
select N'1.29.32.35' union all
select N'57.58.59.60.62.63' union all
select N'57.65.66.67.68'
Go
--测试数据结束
DECLARE @k INT = (SELECT MAX(len(col)-len(replace(col,'.',''))) FROM #T)
DECLARE @str NVARCHAR(MAX)=''
SELECT @str=@str+' union select '+dbo.f_getcol(col,'.',@k+1) FROM #T
PRINT STUFF(@str,1,8,'')
IF OBJECT_ID('tempdb..#tmp_1') IS NOT NULL DROP TABLE #tmp_1
CREATE TABLE #tmp_1 (id INT,a1 VARCHAR(50))
INSERT INTO #tmp_1
SELECT 1,'1.25.26.27.28 '
UNION
SELECT 2,'1.29.32.35'
--1.建立函数
CREATE FUNCTION [dbo].[fn_split] ( @String NVARCHAR(MAX) ,
@Str NVARCHAR(20),
@distinct BIT )
RETURNS @table TABLE ( Value NVARCHAR(MAX),orders int)
AS
BEGIN
DECLARE @i INT ,
@j INT ,
@l INT ,
@v NVARCHAR(MAX),
@orders int
SET @i = 0
SET @j = 0
SET @l = LEN(@String)
set @orders=1
WHILE ( @j < @l )
BEGIN
SET @j = CHARINDEX(@Str, @String, @i + 1)
IF @j = 0
SET @j = @l + 1
SET @v = SUBSTRING(@String, @i + 1, @j - @i - 1 )
IF @v <> '' AND (@distinct = 0 OR NOT EXISTS (SELECT 1 FROM @table WHERE Value = @v))
INSERT INTO @table
VALUES ( LTRIM(RTRIM(@v)),@orders)
SET @i = @j + LEN(@Str) - 1
set @orders=@orders+1
END
RETURN
END
--2.用下面语句
IF OBJECT_ID('tempdb..#tmp_11') IS NOT NULL DROP TABLE #tmp_11
SELECT b.Value
FROM #tmp_1 a
CROSS APPLY(SELECT Value FROM dbo.fn_split(a.a1,'.',1)) b
这样?