.net - What's a good way to trim all whitespace characters from a string in T-SQL without UDF and without CLR? -

the .net function string.trim trims rather extensive set of whitespace characters. how exact behavior emulated in best way t-sql?

ltrim , rtrim trim space character not enough.

this easy sql clr not possible , has quite high invocation cost. avoid sql clr.

also, scalar udfs force serial plans , slow invoke. should not scalar udf, either.

given these constraints what's best way go this?

this code provides pattern can extend handle whitespace of choice modified ltrim.

declare @tab nvarchar(1) = nchar( 9 ); declare @space nvarchar(1) = nchar( 32 );  declare @samples table ( string nvarchar(16) ); insert @samples ( string ) values   ( 'foo' ),   ( @tab + 'foo' ),   ( @space + 'foo' ),   ( @space + @tab + 'foo' ),   ( @tab + @space + 'foo' ); select string, len( string ) [length], patindex( '%[^' + @tab + @space + ']%', string ) - 1 [whitespacecount]   @samples; 

the reverse function can used implement modified version of rtrim.

newer update: following code uses list of whitespace characters used in .net framework 4. works around feature of len not counting trailing blanks.

declare @tab nvarchar(1) = nchar( 9 ); declare @space nvarchar(1) = nchar( 32 );  declare @samples table ( string nvarchar(16) ); insert @samples ( string ) values   ( 'foo' ),   ( @tab + 'foo' ),   ( @space + 'foo' ),   ( @space + @tab + 'foo' ),   ( @tab + @space + 'foo' ),   ( @tab + 'foo' + @space ),   ( @space + 'foo' + @tab ),   ( @space + @tab + 'foo' + @tab + @space ),   ( @tab + @space + 'foo' + @space + @tab ),   ( 'foo' + @tab ),   ( null ),   ( '           ' ),   ( @space + null + @tab + @tab ),   ( '' ),   ( 'hello world!' );  declare @whitespacepattern nvarchar(100) = n'%[^' +   nchar( 0x0020 ) + nchar( 0x00a0 ) + nchar( 0x1680 ) + nchar( 0x2000 ) +   nchar( 0x2001 ) + nchar( 0x2002 ) + nchar( 0x2003 ) + nchar( 0x2004 ) +   nchar( 0x2005 ) + nchar( 0x2006 ) + nchar( 0x2007 ) + nchar( 0x2008 ) +   nchar( 0x2009 ) + nchar( 0x200a ) + nchar( 0x202f ) + nchar( 0x205f ) +   nchar( 0x3000 ) + nchar( 0x2028 ) + nchar( 0x2029 ) + nchar( 0x0009 ) +   nchar( 0x000a ) + nchar( 0x000b ) + nchar( 0x000c ) + nchar( 0x000d ) +   nchar( 0x0085 ) + n']%'; -- nb:   len   function not count trailing spaces. --     use   datalength   instead. analyzedsamples (   select string, datalength( string ) / datalength( nchar( 42 ) ) [stringlength],     patindex( @whitespacepattern, string ) - 1 [leftwhitespace],     patindex( @whitespacepattern, reverse( string ) ) - 1 [rightwhitespace]   @samples ),   trimmedsamples (   select string, stringlength, [leftwhitespace], [rightwhitespace],     case       when string null null       when leftwhitespace = -1 n''       else substring( string, leftwhitespace + 1, stringlength - leftwhitespace )       end [ltrim],     case       when string null null       when rightwhitespace = -1 n''       else reverse( substring( reverse( string ), rightwhitespace + 1, stringlength - rightwhitespace ) )       end [rtrim],     case       when string null null       when leftwhitespace = -1 n''       else substring( string, leftwhitespace + 1, stringlength - leftwhitespace - rightwhitespace )       end [trim]     analyzedsamples )   select n'"' + string + n'"' [string], stringlength, [leftwhitespace], [rightwhitespace],     n'"' + [ltrim] + n'"' [ltrim], datalength( [ltrim] ) / datalength( nchar( 42 ) ) [ltrimlength],     n'"' + [rtrim] + n'"' [rtrim], datalength( [rtrim] ) / datalength( nchar( 42 ) ) [rtrimlength],     n'"' + [trim] + n'"' [trim], datalength( [trim] ) / datalength( nchar( 42 ) ) [trimlength]     trimmedsamples;