So, anyway ... check this against the posted "What I have tried:"
CREATE TABLE [tbl_SS_stringsplit](
[EmailThreadId] int,
[LoadId] int,
[LoadDocumentId] int,
[EmailThreadParentHashcode] NVARCHAR(256),
[EmailThreadChildHashcode] NVARCHAR (1100),
[EmailThreadChildHashcodeCount] int
)
I'm doing this because I can copy directly from the code dump you've provided and get a good purchase on the data ...
BULK INSERT [tbl_SS_stringsplit]
FROM 'C:\Users\SS\tabledata.txt'
SELECT [EmailThreadId]
,[LoadId]
,[LoadDocumentId]
,[EmailThreadParentHashcode]
,[EmailThreadChildHashcode]
,[EmailThreadChildHashcodeCount]
FROM [tbl_SS_stringsplit]
The data comes back alright and I checked that by performing this query:
SELECT [EmailThreadId]
,[LoadId]
,[LoadDocumentId]
,[EmailThreadParentHashcode]
,[EmailThreadChildHashcode]
,[EmailThreadChildHashcodeCount]
FROM [tbl_SS_stringsplit]
GO
To get this (abbreviated):
EmailThreadId LoadId LoadDocumentId EmailThreadParentHashcode EmailThreadChildHashcode EmailThreadChildHashcodeCount
1 18 13388 cc4daed8ce8114a66cb914eb2ce9fa75 NULL 0
2 18 13389 afbfe22c38834f0a8838d8bcf75dbab9 afbfe22c38834f0a8838d8bcf75dbab9bb7f39e1781ce52a587f8380fafde71d 1
3 18 13391 41a9539ab798aba22485831373780218 41a9539ab798aba22485831373780218a0fe9b1f2fcfa25380c8abcac1cebb9a 1
4 18 13392 a52053d8f4b2d2302b79a0ebef617be4 a52053d8f4b2d2302b79a0ebef617be46ee25b4c32f9b2992743cd71184aeb34 1
5 18 13393 684b8eb73d3faa9ca3e18de471c6880a NULL 0
6 18 13394 5af02be12a4f8e626e15262c6633a431 1b1b00ac5c9ffd1dbeaf10877e86822e6abdf163046bc08c2f31b737542aeb6,5af02be12a4f8e626e15262c6633a4311b1b00ac5c9ffd1dbeaf10877e86822e 2
.
.
.
20 19 13423 d658fffb9710b42f81aadef034c2ced4 d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe 1
21 19 13424 d658fffb9710b42f81aadef034c2ced4 eac8cbfb1aa25bc4c451efaec6c5f86d74e4fde19c5f57970480fc3924558506,53b9e269f5a156f19f9297a4c467be64eac8cbfb1aa25bc4c451efaec6c5f86d,cebdb5365b0f764b28ebea3325aa3d8953b9e269f5a156f19f9297a4c467be64,eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89,7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e,d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe 6
So, here's the alteration to the FUNCTION you've posted"
Declare @spli table
(
DocId INT,
Dataa NVARCHAR(1000)
)
INSERT INTO @spli (DocId,Dataa) (
--SELECT LoadDocumentId,value
SELECT [LoadDocumentId], [keyword]
FROM [tbl_SS_stringsplit] [a]
--CROSS APPLY STRING_SPLIT(EmailThreadChildHashcode, ',')
CROSS APPLY STRING_SPLIT([a].[EmailThreadChildHashcode], ',') [b]
)
SELECT * FROM @spli
... in order to get ANYTHING back, by the way ... which looks like this (in its entirety):
DocId Dataa
13388 NULL
13389 afbfe22c38834f0a8838d8bcf75dbab9bb7f39e1781ce52a587f8380fafde71d
13391 41a9539ab798aba22485831373780218a0fe9b1f2fcfa25380c8abcac1cebb9a
13392 a52053d8f4b2d2302b79a0ebef617be46ee25b4c32f9b2992743cd71184aeb34
13393 NULL
13394 1b1b00ac5c9ffd1dbeaf10877e86822e6abdf163046bc08c2f31b737542aeb6
13394 5af02be12a4f8e626e15262c6633a4311b1b00ac5c9ffd1dbeaf10877e86822e
13395 NULL
13398 NULL
13399 d63fc2c2c869f796088831c1257ce0464fbcc7891545f2d6fbe31d83405a5e06
13399 595d80afcdc22eca596e9953a8ca164dd63fc2c2c869f796088831c1257ce046
13361 NULL
13362 aebbb64f3c3293c78188a8f0e316e8338fbf58a7b42e1875287c0c0b31b004d4
13362 8fbf58a7b42e1875287c0c0b31b004d4aebbb64f3c3293c78188a8f0e316e833
13363 NULL
13416 NULL
13417 d658fffb9710b42f81aadef034c2ced450ce8fc0fd6461364168fce14c51a3c3
13418 515c78fada1b30f404b82e30e7d4025fcf0075c15f1ee19485c1f82e948964a1
13419 cebdb5365b0f764b28ebea3325aa3d8953b9e269f5a156f19f9297a4c467be64
13419 eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89
13419 7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e
13419 d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe
13420 NULL
13421 eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89
13421 7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e
13421 d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe
13422 NULL
13423 d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe
13424 eac8cbfb1aa25bc4c451efaec6c5f86d74e4fde19c5f57970480fc3924558506
13424 53b9e269f5a156f19f9297a4c467be64eac8cbfb1aa25bc4c451efaec6c5f86d
13424 cebdb5365b0f764b28ebea3325aa3d8953b9e269f5a156f19f9297a4c467be64
13424 eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89
13424 7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e
13424 d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe
Can you see where I'm going with this now after I've modified the code you posted?
This is NOT the solution you're asking for but I'm thinking using STRING_SPLIT was the hang-up you were hoping someone could help you with.