1

Probably it is very easy but for some reason, I can't manage to do it.

The problem is when I remove all the spaces\new lines with CHAR(13) and CHAR(10) and RTRIM it removes all of the spaces and new lines in both directions.

DECLARE @txt NVARCHAR(1000);
SET @txt = '

Some Text

';

Expected Result:

@txt = '

Some Text';
NRKirby
  • 1,584
  • 4
  • 21
  • 38
Misha Zaslavsky
  • 8,414
  • 11
  • 70
  • 116
  • 2
    Possible duplicate of [SQL Remove only leading or trailing carriage returns](https://stackoverflow.com/questions/31035836/sql-remove-only-leading-or-trailing-carriage-returns) – Juan Carlos Oropeza Oct 24 '17 at 14:32
  • It's *not* easy. SQL isn't a string manipulation language. Ideally, data cleaning should be performed *before* inserting the data in the database. Before SQL Server 2016 you'd have to use a very complex query or an external program written eg in C#, to clean text like this. In SQL Server 2016 you can use an R script too. SQl Server 2017 adds Python support as well – Panagiotis Kanavos Oct 24 '17 at 14:49
  • Have you even tried to Google this problem???? There are tons of references on the web. – Eric Oct 24 '17 at 14:50
  • 2
    @Eric tons of very complicated and/or slow solutions. The OP is asking to replace *newlines* at the end of the string. That would be easy with a regex. It's not easy with SQL – Panagiotis Kanavos Oct 24 '17 at 14:51

1 Answers1

1

For such tasks it will be nice to implement SQL CLR function that introduce regex functionality in SQL Server. For example:

DECLARE @txt NVARCHAR(1000);
SET @txt = '

Some Text


';

SELECT [dbo].[fn_Utils_RegexReplace] (@txt, '[\s\n]+$', '');

You can find more information about how to create such function here.

For newer version of SQL Server (starting from SQL Server 2016) you have options to use R for such operations like it is mention in the comments.

Anyway, I am recommended to read about SQL CLR objects that can be created (functions, triggers, types, aggregates) as you can solve various of tasks using .net.

gotqn
  • 42,737
  • 46
  • 157
  • 243