How to modify the SQL code, get the following results-CodePudding

Hellome folder has 119 TXT documents, six in each row record number, and some of them TXT documents, data format for 6 or 1 2 3 4 5 6
The

reference

1,4,11,23,32,55
3 5 11 12 21 and 27
11,15,21,23,27,39
1,2,3,4,5,9
3,5,11,12,21,27
11 15 21, 23 and 27 39
1 2 3 4 5 39
22 and 24, 25, 29, 31 and 32
1 4 November 23 32 to 55

Requirements:
1. Duplicate records as long as you take time,
2. A comma between the original record Numbers don't,
3. Every single digits into double digits,
6 or 4 fields notext data format 1 2 3 4 5 6 converts 01 02, 03 04 05 06

How to modify the code below, the following results
The

reference

notext
01, 02, 03 04 05 09
01, 02, 03 04 05 39
01 04 11 23 32 to 55
03 05 November 12 21 27
11 15 21, 23 and 27 39
22 and 24, 25, 29, 31 and 32

 
If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1 
Go 
The create table # test1 ([notext] nvarchar (20)) 

DECLARE @ SQL NVARCHAR (MAX), @ INT I; 
SELECT @ SQL=N '@ I=119; 
WHILE @ I & gt; 0 
The BEGIN 
The SET @ SQL=N 'BULK INSERT # test1 FROM' 'E: \ LOTTData \ hellome \' + CONVERT (VARCHAR (2), @ I) 
+ N ". TXT "' WITH 
(
FIELDTERMINATOR=' ' ' ', 
ROWTERMINATOR='\ n' 
) '; 
PRINT @ SQL; 
The EXEC (@ SQL); 
The SET @ I=@ I - 1; 
END;

CodePudding user response:

With [BULK INSERT] statement, will be very good processing,
Look at the official example:
BULK INSERT (Transact - SQL)

CodePudding user response:

After import data processing:

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

SELECT the REPLACE (REPLACE (Note, 'and', '), ', ', ') Note, S.N ewNote 
The FROM @ t t 
The OUTER APPLY (SELECT CASE WHEN LEN (newNote. Value)=1 THEN '0' + newNote. Value 
The ELSE '+ newNote. Value 
END 
The FROM STRING_SPLIT (REPLACE (REPLACE (note, 'and', '), ', ', '), ' ') AS newNote 
FOR XML PATH (') S (NewNote); 
- remove the duplicates 
SELECT DISTINCT S.N ewNote 
The FROM @ t t 
The OUTER APPLY (SELECT CASE WHEN LEN (newNote. Value)=1 THEN '0' + newNote. Value 
The ELSE '+ newNote. Value 
END 
The FROM STRING_SPLIT (REPLACE (REPLACE (note, 'and', '), ', ', '), ' ') AS newNote 
FOR XML PATH (') S (NewNote);

CodePudding user response:

There are a number of Spaces, you can use the front STUFF out:

 
- remove the duplicates 
SELECT DISTINCT STUFF (S.N ewNote, 1, 1, ' ') AS NewNote 
The FROM @ t t 
The OUTER APPLY (SELECT CASE WHEN LEN (newNote. Value)=1 THEN '0' + newNote. Value 
The ELSE '+ newNote. Value 
END 
The FROM STRING_SPLIT (REPLACE (REPLACE (note, 'and', '), ', ', '), ' ') AS newNote 
FOR XML PATH (') S (NewNote);

CodePudding user response:

Thanks for 2 floor Hello World, reply

thank you learned

Hello World reference 2 floor, response:

after import data processing:
[code=SQL]
DECLARE @ t TABLE (Note NVARCHAR (MAX))
INSERT the @ t (Note) VALUES
,4,11,23,32,55 (' 1 '), (' 3 5 11 12 21 and 27), (' 11,15,21,23,27,39),
,2,3,4,5,9 (' 1 '), (' 3,5,11,12,21,27), (' 11 15 21, 23 and 27 39),
39 '(' 1, 2, 3, 4, 5), (' 22 and 24 25 29, 31 and 32), (' 1 4 November 23 32 to 55)

SELECT the REPLACE (REPLACE (Note, 'and', '), ', ', ') Note, S.N ewNote
The FROM @ t t

You also need to solve the problem of the following
Hellome there are 119 TXT documents folder
Need to directly from hellome import 119 TXT document data
Import data path for
FROM ' 'E: \ LOTTData \ hellome \'
Problem a: how to modify the code to import the FROM 'E: \ LOTTData \ hellome \' rather than import FROM the @ t
Problem two: error code execution

reference

message 208, level 16, state 1, line 8
Object name 'STRING_SPLIT' invalid,

CodePudding user response:

LZ is using SQL server 2014

 create function f_split (@ SourceSql varchar (8000), @ StrSeprate varchar (10)) 
Returns the @ temp table (a varchar (100)) 
, realizing the function of the split function 
- date: 2018-5-14 
As 
The begin 
Declare @ I int 
The set @ SourceSql=rtrim (ltrim (@ SourceSql)) 
The set @ I=charindex (@ StrSeprate, @ SourceSql) 
While @ i>=1 
The begin 
Insert the @ temp values (left (@ SourceSql, @ I - 1)) 
Set @ SourceSql=the substring (@ SourceSql, @ I + 1, len (@ SourceSql) - @ I) 
The set @ I=charindex (@ StrSeprate, @ SourceSql) 
End 
If @ SourceSql<> '
Insert the @ temp values (@ SourceSql) 
Return 
End

Realizing the function of the split function cannot use?

CodePudding user response:

Leading into the alternative processing again, at least can split function, low version with the custom

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull