如何修改SQL的代码,得到以下的结果

RICHEER COCA 2021-05-11 12:27:21
hellome文件夹里有119个TXT文档,每行记录里有6个数,其中有些TXT文档,数据格式为1,2,3,4,5,6或1 2 3 4 5 6
引用
1,4,11,23,32,55
3 5 11 12 21 27
11,15,21,23,27,39
1,2,3,4,5,9
3,5,11,12,21,27
11 15 21 23 27 39
1 2 3 4 5 39
22 24 25 29 31 32
1 4 11 23 32 55


要求:
1.重复记录只要取一次,
2.原纪录中数字间的逗号不要,
3.凡是一位数变成2位数,
4.字段notext的数据格式1,2,3,4,5,6或1 2 3 4 5 6转换成01 02 03 04 05 06

如何修改下面的代码,得到以下的结果
引用
notext
01 02 03 04 05 09
01 02 03 04 05 39
01 04 11 23 32 55
03 05 11 12 21 27
11 15 21 23 27 39
22 24 25 29 31 32


if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
create table #test1([notext] nvarchar(20))

DECLARE @sql NVARCHAR(MAX), @i INT;
SELECT @sql = N'', @i = 119;
WHILE @i > 0
BEGIN
SET @sql = N'BULK INSERT #test1 FROM ''E:\LOTTData\hellome\' + CONVERT(VARCHAR(2), @i)
+ N'.txt'' WITH
(
FIELDTERMINATOR ='' '',
ROWTERMINATOR = ''\n''
)' ;
PRINT @sql;
EXEC (@sql);
SET @i = @i - 1;
END;
...全文
159 点赞 收藏 15
写回复
15 条回复
RICHEER COCA 05月12日
引用
消息 207,级别 16,状态 1,第 23 行 列名 'Note' 无效。
先导入再替换处理,#t 里一旦有 NULL 数据 就报错,能不能批量导入进行替换处理?我想学习这种方法,麻烦大神指教
回复 点赞
RICHEER COCA 05月12日
引用 8 楼 RINK_1 的回复:
借用#2的数据
6 楼Hello World的思路是先导入再替换处理,能不能批量导入进行替换处理?我想学习这种方法,麻烦大神指教
---  先导入  #t

if object_id('tempdb.dbo.#t') is not null drop table #t
go
create table #t ([notext] nvarchar(20)) -----   先导入  #t
 
DECLARE @sql NVARCHAR(MAX), @i INT;
SELECT  @sql = N'', @i = 147;
WHILE @i > 0
    BEGIN
        SET @sql = N'BULK INSERT #test1 FROM ''E:\LOTTData\hellome\' + CONVERT(VARCHAR(2), @i)
                   + N'.txt'' WITH       
					(         
					FIELDTERMINATOR ='' '',
					ROWTERMINATOR = ''\n''    
					)'      ;
        PRINT @sql;
        EXEC (@sql);
        SET @i = @i - 1;
    END;
  --- 再替换处理
;WITH CTE_1
AS
(SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN,REPLACE(REPLACE(Note,', ',','),' ',',') AS NOTE_NEW FROM #T),

CTE_2
AS
(SELECT A.*,B.NUMBER,RIGHT('0'+SUBSTRING(NOTE_NEW,number,CHARINDEX(',',NOTE_NEW+',',NUMBER)-NUMBER),2) AS SINGLE_CHAR
FROM CTE_1 A
JOIN master.dbo.spt_values B ON number=CHARINDEX(',',','+NOTE_NEW,NUMBER)
WHERE TYPE='P')

SELECT RN,STUFF((SELECT ' '+SINGLE_CHAR FROM CTE_2 WHERE RN=A.RN ORDER BY number FOR XML PATH('')),1,1,'') AS NOTE_FINAL
FROM CTE_2 AS A
GROUP BY RN



回复 点赞
RICHEER COCA 05月12日
引用 12 楼 文盲老顾 的回复:
引用
消息 208,级别 16,状态 1,第 13 行 对象名 'master.dbo.regexmatcheswithgroup' 无效。
请问什么原因?学习中,收益匪浅,感谢大佬
回复 点赞
morliz子轩 05月12日
两位大神的力作,厉害了。我也是受益非浅,感谢啊
回复 点赞
文盲老顾 05月12日

declare @n varchar(max)
set @n = '1,4,11,23,32,55
3 5 11 12 21 27
11,15,21,23,27,39
1,2,3,4,5,9
3,5,11,12,21,27
11 15 21 23 27 39
1 2 3 4 5 39
22 24 25 29 31 32
1 4 11 23 32 55'

select distinct *,master.dbo.regexreplace('0'+no1+' 0'+no2+' 0'+no3+' 0'+no4+' 0'+no5+' 0'+no6,'\d*(\d{2})(?!\d)','$1') 
from (
	select [1] as no1,[2] as no2,[3] as no3,[4] as no4,[5] as no5,[6] as no6
	from (
		select sn,convert(varchar(3),match) as match,[group] as grp 
		from master.dbo.regexmatcheswithgroup(@n,'(\d+)[^\d\r\n]+(\d+)[^\d\r\n]+(\d+)[^\d\r\n]+(\d+)[^\d\r\n]+(\d+)[^\d\r\n]+(\d+)') a
	) a
	pivot(max(match) for grp in ([0],[1],[2],[3],[4],[5],[6])) p
) a




no1  no2  no3  no4  no5  no6  
---- ---- ---- ---- ---- ---- -------------------------------------------------------------
1    2    3    4    5    39   01 02 03 04 05 39
1    2    3    4    5    9    01 02 03 04 05 09
1    4    11   23   32   55   01 04 11 23 32 55
11   15   21   23   27   39   11 15 21 23 27 39
22   24   25   29   31   32   22 24 25 29 31 32
3    5    11   12   21   27   03 05 11 12 21 27

(6 行受影响)
回复 点赞
Hello World, 05月12日
你创建的表字段是notext,不是Note,得保持一致才行
回复 点赞
文盲老顾 05月12日
自定义函数,正则支持 https://download.csdn.net/download/superwfei/18609994
回复 点赞
RINK_1 05月11日
借用#2的数据

CREATE TABLE #T (Note NVARCHAR(MAX))
INSERT #T VALUES
('1, 4, 11, 23, 32, 55'),('3 5 11 12 21 27'),('11,15,21,23,27,39'),
('1,2,3,4,5,9'),('3,5,11,12,21,27'),('11 15 21 23 27 39'),
('1 2 3 4 5 39'),('22 24 25 29 31 32'),('1 4 11 23 32 55')

WITH CTE_1
AS
(SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN,REPLACE(REPLACE(Note,', ',','),' ',',') AS NOTE_NEW FROM #T),

CTE_2
AS
(SELECT A.*,B.NUMBER,RIGHT('0'+SUBSTRING(NOTE_NEW,number,CHARINDEX(',',NOTE_NEW+',',NUMBER)-NUMBER),2) AS SINGLE_CHAR
FROM CTE_1 A
JOIN master.dbo.spt_values B ON number=CHARINDEX(',',','+NOTE_NEW,NUMBER)
WHERE TYPE='P')

SELECT RN,STUFF((SELECT ' '+SINGLE_CHAR FROM CTE_2 WHERE RN=A.RN ORDER BY number FOR XML PATH('')),1,1,'') AS NOTE_FINAL
FROM CTE_2 AS A
GROUP BY RN



回复 点赞
Hello World, 05月11日
前面有个多的空格,可以用STUFF去掉:

--去掉重复项
SELECT  DISTINCT STUFF(S.NewNote,1,1,'') AS NewNote
FROM    @t t
        OUTER APPLY (   SELECT  CASE WHEN LEN(newNote.value) = 1 THEN ' 0' + newNote.value
                                     ELSE ' ' + newNote.value
                                END
                        FROM    STRING_SPLIT(REPLACE(REPLACE(note, ',', ' '), ',', ' '), ' ') AS newNote
                        FOR XML PATH('')) S(NewNote);
回复 点赞
Hello World, 05月11日
导入后的数据处理:

DECLARE @t TABLE(Note NVARCHAR(MAX))
INSERT @t (Note) VALUES
('1,4,11,23,32,55'),('3 5 11 12 21 27'),('11,15,21,23,27,39'),
('1,2,3,4,5,9'),('3,5,11,12,21,27'),('11 15 21 23 27 39'),
('1 2 3 4 5 39'),('22 24 25 29 31 32'),('1 4 11 23 32 55')

SELECT  REPLACE(REPLACE(Note, ',', ' '), ',', ' ') note, S.NewNote
FROM    @t t
        OUTER APPLY (   SELECT  CASE WHEN LEN(newNote.value) = 1 THEN ' 0' + newNote.value
                                     ELSE ' ' + newNote.value
                                END
                        FROM    STRING_SPLIT(REPLACE(REPLACE(note, ',', ' '), ',', ' '), ' ') AS newNote
                        FOR XML PATH('')) S(NewNote);
--去掉重复项
SELECT  DISTINCT S.NewNote
FROM    @t t
        OUTER APPLY (   SELECT  CASE WHEN LEN(newNote.value) = 1 THEN ' 0' + newNote.value
                                     ELSE ' ' + newNote.value
                                END
                        FROM    STRING_SPLIT(REPLACE(REPLACE(note, ',', ' '), ',', ' '), ' ') AS newNote
                        FOR XML PATH('')) S(NewNote);
回复 点赞
morliz子轩 05月11日
用【BULK INSERT】语句来实现,会很好的处理。 看官方实例: BULK INSERT (Transact-SQL)
回复 点赞
RICHEER COCA 05月11日
引用 6 楼 Hello World, 的回复:
先导入再替换处理,至少split函数能用就行了,低版本就用自定义的
麻烦 用修改后的代码指导一下,试了N遍 但不得要领
回复 点赞
Hello World, 05月11日
先导入再替换处理,至少split函数能用就行了,低版本就用自定义的
回复 点赞
RICHEER COCA 05月11日
LZ使用的是sql server 2014
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2018-5-14
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
实现split功能 的函数不能用?
回复 点赞
RICHEER COCA 05月11日
感谢 2 楼 Hello World, 的回复感谢学习了
引用 2 楼 Hello World, 的回复:
导入后的数据处理: [code=sql] DECLARE @t TABLE(Note NVARCHAR(MAX)) INSERT @t (Note) VALUES ('1,4,11,23,32,55'),('3 5 11 12 21 27'),('11,15,21,23,27,39'), ('1,2,3,4,5,9'),('3,5,11,12,21,27'),('11 15 21 23 27 39'), ('1 2 3 4 5 39'),('22 24 25 29 31 32'),('1 4 11 23 32 55') SELECT REPLACE(REPLACE(Note, ',', ' '), ',', ' ') note, S.NewNote FROM @t t
还需要解决以下的问题 hellome文件夹里有119个TXT文档 需要直接从hellome导入119个TXT文档数据 导入数据路径为 FROM ''E:\LOTTData\hellome\' 问题一:如何修改代码导入FROM ''E:\LOTTData\hellome\' 而不是导入 FROM @t 问题二:代码执行出错
引用
消息 208,级别 16,状态 1,第 8 行 对象名 'STRING_SPLIT' 无效。
回复 点赞
发动态
发帖子
疑难问题
创建于2007-09-28

9304

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
社区公告
暂无公告