I have a very odd scenario, where we're migrating from one database to another. The new database seems fantastic in many ways, however the history log for each contact is restricted.
In the current database, the string lengths are limited to 32,000 characters [Long Text Area(32000)].
I was wanting to be clever, and see if that text can wrap onto another record. It is OK if all the other fields are the same for each wrap.
Any ideas?
You can do that however you may not want to as the other columns are now repeated for each part of the CLOB column. Unless there is a business reason to need to do this denormalization I wouldn't do it. Why were you asking?
I'm migrating data from one database to another, using MS Server 2005 to export the data into Saleforce dataloader. The allowable string length in the current database is very large, but the maximum string length in the new system is 32,000. I cannot create another field, as the system doesn't allow that. If I can wrap the string into the next record, then I won't lose any data. (they're basically customer notes, which can sometimes get very very long overtime)
Does that clarify the problem?
The basic principle is something like:
Declare @x varchar(100) = 'A string long enough that we need to break it into multiple lines'
Declare @charsPerLine int = 20
Select
STRING_AGG(X.letter,'') WIthin Group (Order By X.position)
From
(
select
N.value as position,
substring(@x,N.value,1) as letter,
N.value/@charsPerLine As rowNumber
from Numbers N
where N.value <= len(@x)
) X
Group By X.rowNumber
Order By X.rowNumber
If you're using 2005 you'll need to substitute the String_Agg with your favourite string concatenation trick and you'll need a table of numbers or similar
Hmmmm. It would appear that I cannot assign a default value to a local variable in MS Server 2005. I've not done that in this environment before, so maybe I'm missing something else?
Yeah, in 2005 you had to declare variable separate from initializing them, e.g.
Declare @charsPerLine Int
Set charsPerLine = 25
Catches me out every time I work on one of our old 2005 boxes!
Thanks Andy, but I'm simply not clever enough to follow. I've tried interpretting your suggestion into my context a few times, and I'm not winning
If it helps, the database table is called sysdba.HISTORY, and I want to wrap this concatenated field for every 32,000 characters.
sysdba.HISTORY.Longnotes + ' | ' + sysdba.HISTORY.Username
If it breaks mid word, I can live with that, but would prefer a break after a whole word.
LongNotes is TEXTBLOB(text) and Username is CHAR(12).
Are you still able to help?
Thanks
TEXT columns? Yeah, that gets very icky very fast. Here is a really vague stab at the principal idea:
If Object_ID('tempdb..#Test') Is Not Null Drop Table #Test
Create Table #Test
(
EntryID Int,
SomeData VarChar(20),
UserName Char(12),
LongNotes Text
)
Declare @BigText VarChar(Max)
Set @BigText = 'The quick brown fox jumps over the lazy dog. '
Set @BigText = REPLICATE(@BigText, 3000)
Declare @BigText2 VarChar(Max)
Set @BigText2 = 'It was a bright cold day in April, and the clocks were striking thirteen. '
Set @BigText2 = REPLICATE(@BigText2, 7000)
Insert Into #Test
Select 1, 'Something', 'Andy', ''
Union All Select 2, 'Something Else', 'Bob', ''
Union All Select 3, 'No Notes', 'Frank', ''
-- Put something in the TEXT columns
Declare @pointer Binary(16)
Select @pointer = TEXTPTR(LongNotes) From #Test Where EntryID = 1
WriteText #Test.LongNotes @Pointer @BigText
Select @pointer = TEXTPTR(LongNotes) From #Test Where EntryID = 2
WriteText #Test.LongNotes @Pointer @BigText2
;
With Lengths As
(
Select EntryID, DATALENGTH(longnotes) TotalLength from #Test
),
Chunks As
(
Select EntryID, 0 As ChunkStart, 7999 As ChunkEnd, TotalLength, 0 As ChunkNumber, 0 As ChunkOrder From Lengths
Union All
Select EntryID, NewChunkStart, NewChunkEnd, TotalLength, NewChunkStart / 32000, NewChunkStart % 32000 / 8000 From Chunks
Cross Apply
(
Select ChunkStart + 8000 As NewChunkStart, ChunkEnd + 8000 As NewChunkEnd
) X
Where ChunkStart + 8000 < TotalLength
),
ChunkText As
(
Select T.EntryId, C.ChunkStart, SubString(T.LongNotes, C.ChunkStart, C.ChunkEnd) As ChunkText, ChunkNumber, ChunkOrder
From #Test T
Join Chunks C On T.EntryID = C.EntryID
)
Select
T.EntryID,
T.SomeData,
T.UserName,
ROW_NUMBER() Over (Partition By T.EntryID Order By C1.ChunkStart) As Ordinal,
Concat(C1.ChunkText, C2.ChunkText, C3.ChunkText, C4.ChunkText) As Text32000
From #Test T
Left Join ChunkText C1 On C1.EntryID = T.EntryID And C1.ChunkOrder = 1
Left Join ChunkText C2 On C1.EntryID = C2.EntryID And C1.ChunkNumber = C2.ChunkNumber And C2.ChunkOrder = 2
Left Join ChunkText C3 On C1.EntryID = C3.EntryID And C1.ChunkNumber = C3.ChunkNumber And C2.ChunkOrder = 3
Left Join ChunkText C4 On C1.EntryID = C4.EntryID And C1.ChunkNumber = C4.ChunkNumber And C2.ChunkOrder = 4
Note that due to the joys of TEXT you have to jump through some hoops not to avoid having things get truncated when they go over 8000 bytes. I also haven't added the username into the string, I'm not sure there is a cleaner way to do it other than either:
a) Reconstruct the entire TEXT column into a VarChar(Max), add it, then split that up into chunks.
b) Split it to slightly less than 8000 byte chunks so that there will be "spare capacity" in the 32000 characters to squeeze in the username at the end without having to worry about it overflowing.
I think I've stayed "2005 clean" with all my SQL, but I've only got a 2017 install to test it on so I can't entirely swear to it.