We have a column field that contains more than 1000 characters. The vendor asked us to export the data as text file, pipe delimeted. However, they mentioned that if the field is more than 500 characters, it should be separated to another column in the text file. Let's just imagine the string below is 1500 characters long and is found in the column from the table. abcdefghijklmnopqrstuvwxyz They said that they want it separated. That means, each exported text file can have different number of columns. It would look like this in text file after it was been exported abcdefghij | klmnopqrs | tuvwxyz The above is just an example. Each string were cut down to 500 characters but 2 new columns on the text file were added. Can we do that using in a SELECT statement? Oh, I export using Tools->Options->Results tab->then Results to file. I just choose the pipe delimiter.
Assuming we're not talking about a TEXT or NTEXT column, but VARCHAR() or NVARCHAR() something like this might do the trickselect substring(c, 1, 3) + case when len(c) > 3 then '|' else '' end + substring(c, 4, 3) + case when len(c) > 6 then '|' else '' end + substring(c, 7, 3) + case when len(c) > 9 then '|' else '' end + substring(c, 10, 3) + case when len(c) > 12 then '|' else '' end + substring(c, 13, 3) from (select '123456789' c) t I included CASE to suppress unnecessary separators ... I thought about trying the STUFF() function but it returns NULL if you attempt to add a separator after the last character.
It's in TEXT data type. Do I just change the function that will work for TEXT and use the logic you wrote above using case statement?
I got it to work using your logic. Thank you so much! [] select len(cast(legal as varchar(8000))),substring(legal,1,1000) + case when len(cast(legal as varchar(8000))) > 1000 then '|' else '' end + substring(legal, 1001, 1000) + case when len(cast(legal as varchar(8000))) > 2000 then '|' else '' end + substring(legal, 2001, 1000) + case when len(cast(legal as varchar(8000))) > 3000 then '|' else '' end + substring(legal, 3001, 1000) + case when len(cast(legal as varchar(8000))) > 4000 then '|' else '' end + substring(legal, 4001, 1000) + case when len(cast(legal as varchar(8000))) > 5000 then '|' else '' end + substring(legal, 5001, 1000) + case when len(cast(legal as varchar(8000))) > 6000 then '|' else '' end + substring(legal, 6001, 1000) + case when len(cast(legal as varchar(8000))) > 7000 then '|' else '' end + substring(legal, 7001, 1000), * from assign
