0

There are 185.647.711 characters data in only one column and one row. So I wanna see all data. I trying to copy to text file(Ctrl+C Ctrl+P). It just geting almost half of data. İs there any way to see all data?

HKN
  • 13
  • 6
  • Is this even SQL? You want to see everything in a column but when you copy and paste you only see half? Try selecting the column in a select query – JamesS Jul 10 '19 at 15:49
  • 3
    Take a peek at https://stackoverflow.com/questions/11039552/sql-server-field-getting-truncated – John Cappelletti Jul 10 '19 at 15:51
  • Maybe I can split this data using a query . But I dont know how to do. – HKN Jul 10 '19 at 15:51
  • 2
    The first issue is that you have that much data in a single tuple. That is a huge red flag that you need to revisit some design ideas. But don't use SSMS to display data like this. You can create a file directly from a query or create a quick application to do it. – Sean Lange Jul 10 '19 at 15:54
  • Split the string in half like so: `DECLARE @string NVARCHAR(MAX) = '123456789asdfasdf' SELECT SUBSTRING(@string,0,LEN(@string) / 2) AS FirstHalf, SUBSTRING(@string,LEN(@string) / 2,LEN(@string)) AS SecondHalf` – Icculus018 Jul 10 '19 at 15:56
  • 2
    *"So I wanna see all data"* Why? How are you going to consume 185M characters easily and quickly? @SeanLange is right though, don't use SSMS, create an application, or use something like `sqlcmd` to export the value to a file. – Thom A Jul 10 '19 at 15:58
  • 1
    @kristech the problem with that is the max number of characters allowed to be returned in SSMS is 65,535. To capture all the characters for 185,647,711 it would require 2833 splits. – Sean Lange Jul 10 '19 at 16:05
  • 1
    @SeanLange Yes, i do realize that, which is why i didn't put it in an answer. I showed the OP how to split a string because he asked in the comment how to split a string. Anyone using this question as reference should understand the max character output. – Icculus018 Jul 10 '19 at 16:08
  • I'd pay folding money to know how they got that much data into that field in the first place... – Brian Jul 10 '19 at 16:29

2 Answers2

0

I used this code but it is still running. " create table #temp( name nvarchar(max) )

declare @string nvarchar(max) set @string = (SELECT columName FROM TableName where ID = someId)

declare @i int set @i = 50000

while @i < 185647711 begin insert #temp select SUBSTRING(@string,@i-50000,@i) set @i = @i + 50000 end

insert #temp select right(@string,185647711%50000)

select * from #temp drop table #temp " Yeah I know data is soo stupid but there is nothing to do data is data.

HKN
  • 13
  • 6
  • This is just the wrong approach entirely. This requires manipulating and evaluating this same string over and over...approximately 3713 times in fact. Use BCP or a different approach. Copying and pasting this much information is an exercise in futility. – Sean Lange Jul 10 '19 at 18:40
0

You can BCP OUT to a text file and open it with a text editor, either with word-wrap or without if you want to scroll forever to the right.

Russell Fox
  • 5,273
  • 1
  • 24
  • 28