In SQL Sever, if we create a table with column varchar(max) or nvarchar(max), then we can store upto 2 GB of data. However when we retrieve such columns, we can only see the data upto 43679 character. If we want to see complete set of data, we can use below script. User needs to provide following three inputs:
1. @StrLenSplit: It should not be more than 43679
2. @ColumnName: Provide the column name with data type as varchar(max) or nvarchar(max)
3. @TableName: Provide table name
--Required input from user
DECLARE @StrLenSplit BIGINT=4000 --should not be more than 43679
DECLARE @ColumnName VARCHAR(256)='jasonstringNvarchar'
DECLARE @TableName VARCHAR(256)='TableJson'
--Variable Declaration Part
DECLARE @stringlen BIGINT, @ExecuteQuery VARCHAR(2000)
DECLARE @ColumnDetails VARCHAR(1000)
DECLARE @idx INT=1, @SplitCount INT, @StringStartIndex INT=1
SELECT @stringlen=MAX(LEN(jasonstringNvarchar)) FROM TableJson
IF(@stringlen>@StrLenSplit)
BEGIN
SET @ColumnDetails=''
SET @SplitCount=1
WHILE (1=1)
BEGIN
SET @ColumnDetails = @ColumnDetails+' SUBSTRING('+@ColumnName+','+CONVERT(VARCHAR(5),@StringStartIndex)+','+CONVERT(VARCHAR(5),@StrLenSplit)+') AS '+@ColumnName+'_'+CONVERT(VARCHAR(3),@SplitCount)+', '
SET @StringStartIndex=@StrLenSplit+1
SET @StrLenSplit=@StringStartIndex+@StrLenSplit
IF (@StrLenSplit>@stringlen)
BEGIN
SET @StrLenSplit=@stringlen
SET @idx=@idx+1
END
IF(@idx>2)
BEGIN
Break
END
SET @SplitCount=@SplitCount+1
END
END
SET @ColumnDetails = LEFT(@ColumnDetails, LEN(@ColumnDetails) - 1)
SET @ExecuteQuery= 'SELECT '+ @ColumnDetails+' FROM '+@TableName
EXEC( @ExecuteQuery)
