Как вставить разрыв строки в строку SQL Server VARCHAR / NVARCHAR
Я не встречал подобных вопросов по этой теме, и мне пришлось исследовать это для того, над чем я работаю прямо сейчас. Думал, что отправлю ответ на него, если у кого-то еще возникнет такой же вопрос.
@StefanosZilellis и обязательно откройте новое окно запроса, чтобы изменения настроек вступили в силу.
Ответы 11
Я нашел ответ здесь: http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/
Вы просто объединяете строку и вставляете CHAR(13) там, где хотите разрыв строки.
Пример:
DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text
Это распечатает следующее:
This is line 1.
This is line 2.
Но как это сделать программно? Скажем из C#?
ОБНОВЛЕНИЕ: забудьте об этом. Вставляет просто отлично. Это студия управления, которая заменяет табуляции и новые строки пробелами для наглядности.
Кажется, вам нужно использовать PRINT @text, а не SELECT, чтобы получить этот результат.
Кстати: вы также можете использовать NCHAR(0x1234) для получения символа Юникода. Не требуется для вставки разрывов строки, но может пригодиться, если нужно вставить / найти символы Юникода.
В SQL Server 2016 я вижу только две строки, если я использую print вместо select, например: DECLARE @text NVARCHAR(100); SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'; print @text;
@devinbost, потому что сетки результатов SSMS скрыть разрывы строк по умолчанию
Чтобы проверить результат, при использовании SSMS убедитесь, что установлен флажок «Сохранять CR / LF при копировании или сохранении», иначе все вставленные результаты потеряют перевод строки. Вы найдете это в настройках, результатах запроса, сервере sql, результатах в сетку.
Что сказал @DonCheadle, И вам, возможно, придется закрыть вкладку запроса и снова открыть ее, чтобы этот параметр вступил в силу. ;)
После Google ...
Взяв код с сайта:
CREATE TABLE CRLF
(
col1 VARCHAR(1000)
)
INSERT CRLF SELECT 'The quick brown@'
INSERT CRLF SELECT 'fox @jumped'
INSERT CRLF SELECT '@over the '
INSERT CRLF SELECT 'log@'
SELECT col1 FROM CRLF
Returns:
col1
-----------------
The quick brown@
fox @jumped
@over the
log@
(4 row(s) affected)
UPDATE CRLF
SET col1 = REPLACE(col1, '@', CHAR(13))
Похоже, это можно сделать, заменив заполнитель на СИМВОЛ (13)
Хороший вопрос, сам никогда не делал :)
Но если в тексте есть адрес электронной почты? "[email protected]" становится "jon bob.com" (с новой строкой в электронном платье)
@ChrisNash затем используйте другой заполнитель (например, «|», «~» или несколько символов, «! #!»). См. Этот ответ ниже: stackoverflow.com/a/31179/179311.
"CONCAT (CHAR (13), CHAR (10))" ("\ r \ n") было бы лучше для среды Windows, что, как я предполагаю, имеет место (SQL Server) cs.toronto.edu/~krueger/csc209h/tut/line-endings.html
Это недетерминировано. Таблица определяется как набор записей неупорядоченный. Нет гарантии, что ядро базы данных вернет записи в том порядке, в котором они были вставлены. Без второго столбца для сохранения порядка каким-либо образом (подойдет простой IDENTITY()) и ORDER BY в вашем выходном запросе сервер будет возвращать записи в том порядке, в котором он сочтет удобным. Этот май будет в том же порядке, в котором они были вставлены, но серверу разрешено делать это, но он хочет без ORDER BY.
char(13) - это CR. Для разрывов строк CRLF в стиле DOS / Windows вам нужен char(13)+char(10), например:
'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
char (13) + char (10) не работал у меня в окнах. Я просто использовал char (10)
@Nima: некоторые приложения будут использовать один или другой или оба, чтобы показать новую строку, однако многие приложения, в которые вы можете выводить этот текст, потребуют, чтобы оба появлялись последовательно, чтобы обозначить новую строку. Я считаю безопасным использовать оба. Вы можете указать здесь, для каких из ваших приложений это не работает. Я предпочитаю шестнадцатеричные значения CHAR (0x0D) + CHAR (0x0A), но каждому свое.
Я успешно использовал этот метод, но столкнулся с проблемой: как только у вас будет более 480 +, SQL Server начнет жаловаться, что ваш запрос слишком глубоко вложен. Мое решение заключалось в том, чтобы вместо этого использовать ответ Роба Купера, но с гораздо более длинным и неясным токеном.
Это сработало, но мне пришлось сказать дважды: «Это строка 1.» + СИМВОЛ (13) + СИМВОЛ (10) + СИМВОЛ (13) + СИМВОЛ (10) + 'Это строка 2.'
Предоставление \ r \ n означало бы признание того, что регулярные выражения существуют и что есть пользователи, способные понимать и использовать их.
@HBlackorby \ r и \ n на десятилетия предшествовали Java-чему-либо благодаря их использованию в C; и являются стандартными для Python, PHP, Ruby, C++, C# и т. д.
\r\n на C# - это то же самое, что CHAR(13) + CHAR(10) (CHAR(0x0D) + CHAR(0x0A))?
Для тех, кто использует SSMS v16.5 или выше (например, v18.5) и задается вопросом, почему это не копируется правильно, перейдите в Инструменты> Параметры> Результаты запроса> SQL Server> Результаты в сетку> Сохранить CR / LF при копировании или сохранении (изменение вступает в силу после закрытия и повторного открытия окна запроса). Ctrl + Q с Результаты в сетку также доставит вас туда быстрее.
@Kiquenet Просто люди жалуются, что одна система, которую они знают, не универсальна по волшебству для каждого языка программирования, платформы и системы.
@Kiquenet: да, \r относится к 13, а \n - к 10. См. Столбец Dec в asciitable.com
@BaconBits С разумом: экранирование обратной косой черты - это широко используемый, широко понятный способ выполнения действий, тогда как подход SQL CHAR(n) неудобен и его гораздо сложнее читать и использовать. Подобные различия были понятны, учитывая параллельную эволюцию SQL и C, но 40 (50?) Лет спустя непримиримость SQL перед лицом хорошие идеи, которыми стоит поделиться просто разочаровывает, и я чувствую, что это активно вредит популярности SQL (люди стекались к NoSQL, потому что «SQL сложно », и SQL это трудно, потому что это излишне разные по общепринятому соглашению).
Это всегда круто, потому что, когда вы получаете экспортированные списки, скажем, из Oracle, вы получаете записи, занимающие несколько строк, что, в свою очередь, может быть интересно, скажем, для файлов cvs, так что будьте осторожны.
В любом случае, ответ Роба хорош, но я бы посоветовал использовать что-то еще, кроме @, попробуйте еще несколько, наприме𠧧 @@ §§ или что-то в этом роде, так что у него будет шанс на некоторую уникальность. (Но все же помните длину поля varchar / nvarchar, которое вы вставляете ..)
Другой способ сделать это:
INSERT CRLF SELECT 'fox
jumped'
То есть простая вставка разрыва строки в запрос при его написании добавит аналогичный разрыв в базу данных. Это работает в SQL Server Management Studio и Query Analyzer. Я считаю, что это также будет работать в C#, если вы используете знак @ в строках.
string str = @"INSERT CRLF SELECT 'fox
jumped'"
Другими словами, синтаксис языка SQL просто допускает перевод строки в строковые литералы. Это работает во всех движках, которые я пробовал (SQL Server, Oracle, MySQL, PostgreSQL и SQLite).
иногда это случайно перестает работать, если вы используете его в хранимых процедурах
Это было простое и элегантное решение.
Вот функция C#, которая добавляет текстовую строку к существующему текстовому BLOB-объекту, разделенному CRLF, и возвращает выражение T-SQL, подходящее для операций INSERT или UPDATE. В нем есть часть нашей проприетарной обработки ошибок, но как только вы удалите ее, она может быть полезна - я надеюсь на это.
/// <summary>
/// Generate a SQL string value expression suitable for INSERT/UPDATE operations that prepends
/// the specified line to an existing block of text, assumed to have \r\n delimiters, and
/// truncate at a maximum length.
/// </summary>
/// <param name = "sNewLine">Single text line to be prepended to existing text</param>
/// <param name = "sOrigLines">Current text value; assumed to be CRLF-delimited</param>
/// <param name = "iMaxLen">Integer field length</param>
/// <returns>String: SQL string expression suitable for INSERT/UPDATE operations. Empty on error.</returns>
private string PrependCommentLine(string sNewLine, String sOrigLines, int iMaxLen)
{
String fn = MethodBase.GetCurrentMethod().Name;
try
{
String [] line_array = sOrigLines.Split("\r\n".ToCharArray());
List<string> orig_lines = new List<string>();
foreach(String orig_line in line_array)
{
if (!String.IsNullOrEmpty(orig_line))
{
orig_lines.Add(orig_line);
}
} // end foreach(original line)
String final_comments = "'" + sNewLine + "' + CHAR(13) + CHAR(10) ";
int cum_length = sNewLine.Length + 2;
foreach(String orig_line in orig_lines)
{
String curline = orig_line;
if (cum_length >= iMaxLen) break; // stop appending if we're already over
if ((cum_length+orig_line.Length+2)>=iMaxLen) // If this one will push us over, truncate and warn:
{
Util.HandleAppErr(this, fn, "Truncating comments: " + orig_line);
curline = orig_line.Substring(0, iMaxLen - (cum_length + 3));
}
final_comments += " + '" + curline + "' + CHAR(13) + CHAR(10) \r\n";
cum_length += orig_line.Length + 2;
} // end foreach(second pass on original lines)
return(final_comments);
} // end main try()
catch(Exception exc)
{
Util.HandleExc(this,fn,exc);
return("");
}
}
Я попал сюда, потому что был обеспокоен тем, что cr-lfs, указанные мной в строках C#, не отображаются в ответах на запросы SQl Server Management Studio.
Оказывается, они есть, но не отображаются.
Чтобы «увидеть» cr-lfs, используйте инструкцию print, например:
declare @tmp varchar(500)
select @tmp = msgbody from emailssentlog where id=6769;
print @tmp
Запустите это в SSMS, он показывает, как разрывы строк в самом SQL становятся частью строковых значений, охватывающих строки:
PRINT 'Line 1
Line 2
Line 3'
PRINT ''
PRINT 'How long is a blank line feed?'
PRINT LEN('
')
PRINT ''
PRINT 'What are the ASCII values?'
PRINT ASCII(SUBSTRING('
',1,1))
PRINT ASCII(SUBSTRING('
',2,1))
Результат:
Линия 1
Строка 2
Строка 3
Какова длина пустой строки?
2
Каковы значения ASCII?
13
10
Или, если вы предпочитаете указать свою строку в одной строке (почти!), Вы можете использовать REPLACE() следующим образом (при желании используйте CHAR(13)+CHAR(10) в качестве замены):
PRINT REPLACE('Line 1`Line 2`Line 3','`','
')
я бы сказал
concat('This is line 1.', 0xd0a, 'This is line 2.')
или же
concat(N'This is line 1.', 0xd000a, N'This is line 2.')
Также проверьте конфигурацию SSMS в ответе @Trubs: stackoverflow.com/a/59189881/386619
@JayCummins: о ..?
Все эти параметры работают в зависимости от вашей ситуации, но вы можете не увидеть, что ни один из них работает, если вы используете SSMS (как упоминалось в некоторых комментариях, SSMS скрывает CR / LF)
Поэтому вместо того, чтобы объезжать поворот, проверьте эту настройку в
Tools|Options
Я использую SSMS версии 18.2, и у меня возникли проблемы с установкой этого параметра. Мне пришлось проверить это, затем выйти из SSMS и перезапустить. И убедитесь, что у вас работает только 1 экземпляр SSMS. Второй экземпляр перезапишет настройку с исходным значением. Но в конце концов я добился успеха. +1
В некоторых особых случаях это может оказаться полезным (например, рендеринг содержимого ячейки в MS Report)
пример:
select * from
(
values
('use STAGING'),
('go'),
('EXEC sp_MSforeachtable
@command1=''select ''''?'''' as tablename,count(1) as anzahl from ? having count(1) = 0''')
) as t([Copy_and_execute_this_statement])
go
Не могли бы вы добавить еще какое-то объяснение, я понятия не имею, как это работает или как его использовать
этот оператор выводит не строку с CR \ LF в ней, а небольшую таблицу с одним столбцом (с именем [Copy_and_execute_this_statement]) и тремя строками. Это, возможно, подходит для потребителей, которые проглатывают CR \ LF, но могут использовать таблицы (например, MS Report), другим простым примером может быть «select * from (values ('Adam'), ('Eva')) as t ([some_name] ) "
Чтобы проверить результат, при использовании SSMS убедитесь, что установлен флажок «Сохранять CR / LF при копировании или сохранении», иначе все вставленные результаты потеряют перевод строки. Вы найдете это в настройках, результатах запроса, сервере sql, результатах в сетку.