如何修改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;
...全文
259 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
RICHEER COCA 2021-05-12
  • 打赏
  • 举报
回复
引用
消息 207,级别 16,状态 1,第 23 行 列名 'Note' 无效。
先导入再替换处理,#t 里一旦有 NULL 数据 就报错,能不能批量导入进行替换处理?我想学习这种方法,麻烦大神指教
RICHEER COCA 2021-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 2021-05-12
  • 打赏
  • 举报
回复
引用 12 楼 文盲老顾 的回复:
引用
消息 208,级别 16,状态 1,第 13 行 对象名 'master.dbo.regexmatcheswithgroup' 无效。
请问什么原因?学习中,收益匪浅,感谢大佬
morliz子轩 2021-05-12
  • 打赏
  • 举报
回复
两位大神的力作,厉害了。我也是受益非浅,感谢啊
文盲老顾 2021-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, 2021-05-12
  • 打赏
  • 举报
回复
你创建的表字段是notext,不是Note,得保持一致才行
文盲老顾 2021-05-12
  • 打赏
  • 举报
回复
自定义函数,正则支持 https://download.csdn.net/download/superwfei/18609994
RINK_1 2021-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, 2021-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, 2021-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子轩 2021-05-11
  • 打赏
  • 举报
回复
用【BULK INSERT】语句来实现,会很好的处理。 看官方实例: BULK INSERT (Transact-SQL)
RICHEER COCA 2021-05-11
  • 打赏
  • 举报
回复
引用 6 楼 Hello World, 的回复:
先导入再替换处理,至少split函数能用就行了,低版本就用自定义的
麻烦 用修改后的代码指导一下,试了N遍 但不得要领
Hello World, 2021-05-11
  • 打赏
  • 举报
回复
先导入再替换处理,至少split函数能用就行了,低版本就用自定义的
RICHEER COCA 2021-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 2021-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' 无效。

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧