Trim whitespace without affecting inner whitespace
I need to trim spaces (including tabs, newlines, etc.) from a string without affecting inner spaces. For example:
foo
bar
baz
Would become
foo
bar
baz
Of course LTRIM
/ RTRIM
won't be enough, because they only remove spaces. There are a few posts here that show using a method REPLACE
to handle other characters (like this one ), but of course this will also remove internal characters. I haven't been able to find an example here to show how to remove only strings and whitespace characters in a string from a string.
source to share
This functionality is easily handled by SQLCLR. You can write your own function that makes it simple String.Trim()
, or you can load an already written function such as a function String_Trim()
from the SQL # library. I am the author of SQL #, but the feature String_Trim
(and many others, including regex, etc.) are available in the free version.
String_Trim()
removes spaces (tabs, newlines, carriage returns, and spaces) from both ends of the line, without touching spaces between non-whitespace characters. If you copy and paste the example code below, you can see that before and after characters with no spaces, as well as between them, it is a combination of each type of space character (well, tabs are converted to spaces here, so I am typing tabs explicitly).
It's as simple as:
PRINT N'~~' + SQL#.String_Trim(N' ' + NCHAR(9) + N'
' + NCHAR(9) + N' gfgj
lf ' + NCHAR(9) + N'
g
' + NCHAR(9) + N' g
' + NCHAR(9) + N' ') + N'~~';
Output:
~~gfgj
lf
g
g~~
source to share
Here's my current solution, but I would welcome other suggestions. For values VARCHAR
:
DECLARE @str varchar(8000) ,
@pat varchar(100) = '%[^ ' + CHAR(9) + '-' + CHAR(13) + ']%';
SET @str = /* load value */
SET @str = SUBSTRING( @str , PATINDEX( @pat , @str ) ,
DATALENGTH( @str ) -
PATINDEX( @pat , @str ) -
PATINDEX( @pat , REVERSE( @str )) + 2 );
But since characters NVARCHAR
are 2 bytes, in this case you need to divide DATALENGTH
by 2:
DECLARE @str nvarchar(4000) ,
@pat varchar(100) = '%[^ ' + CHAR(9) + '-' + CHAR(13) + ']%';
SET @str = /* load value */
SET @str = SUBSTRING( @str , PATINDEX( @pat , @str ) ,
DATALENGTH( @str ) / 2 -
PATINDEX( @pat , @str ) -
PATINDEX( @pat , REVERSE( @str )) + 2 );
Note that the ( @pat
) pattern used here only covers ASCII characters. This was sufficient for my requirements, but if full Unicode support is needed, you can extend the template. See Wikipedia: space character for a complete list.
And of course it would be pretty easy to convert that to a UDF for easy reuse.
source to share
You should try the following:
DECLARE @testString varchar(255)
set @testString = 'MY STRING '
/*I always use this like a final solution*/
SET @testString = REPLACE(REPLACE(REPLACE(@testString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '')
SELECT @testString
/*
CHAR(9) - Tab
CHAR(10) - New Line
CHAR(13) - Carriage Return
*/
source to share
Use below query:
DECLARE @trimchars VARCHAR(10),@str varchar(max)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
set @str='test string it is'
IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)
IF @str LIKE '%[' + @trimchars + ']' SET @str = SUBSTRING(reverse(@str), PATINDEX('%[^' + @trimchars + ']%', reverse(@str)), 8000)
select @str
source to share
I found it here and changed it a bit. This will work in your case. Any suggestions or feedback would be highly appreciated.
Go
CREATE FUNCTION dbo.LeftTrim(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32)
IF @str LIKE '[' + @trimchars + ']%'
SET @str = STUFF(@str, 1, PATINDEX('%[^' + @trimchars + ']%', @str) - 1, '')
RETURN @str
END
GO
CREATE FUNCTION dbo.RightTrim(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32)
IF @str LIKE '%[' + @trimchars + ']'
SET @str = REVERSE(dbo.LeftTrim(REVERSE(@str)))
RETURN @str
END
GO
CREATE FUNCTION dbo.SuperTrim(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN dbo.LeftTrim(dbo.RightTrim(@str))
END
GO
- Illustration
DECLARE @testStr AS VARCHAR(MAX)=''
SELECT @testStr='my first line ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'second line ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
SELECT dbo.SuperTrim(@testStr) + '--end of line'
source to share