LTRIM RTRIM not working for Chinese string SQL-CodePudding

I have a column named Text which receives from the end user the following string:

'复合模头滤网　φ245　120目*300目 24×120目　'

Which includes a weird space, kind of larger than the regular space, at the end. However it has the same ASCII code as the normal space 32.

I used this SQL code to trim my string but always returning same string without trimming !!!!

LTRIM(RTRIM([Text]))

CodePudding user response：

The solution is to try trim the the character with the ASCII code 32. The following code works perfectly:

TRIM(CHAR(32) from [ShortText])

To check it out if works , I tried it this way :


DECLARE @t TABLE(txt nvarchar(255));
INSERT INTO @t VALUES (TRIM(CHAR(32) from '复合模头滤网　φ245　120目*300目 24×120目　'));

SELECT txt, LEN((txt)), ASCII(RIGHT(txt,1)) AS ASCII_Char
--32=SPACE,--13 CR,--11 LF, 9-tab
FROM @t

CodePudding user response：

This character is U 3000 IDEOGRAPHIC SPACE, and as documented, SQL Server by default only removes U 0020 SPACE.

You can use TRIM(... FROM in modern versions of SQL Server

DECLARE @t nvarchar(1000) = N'复合模头滤网　φ245　120目*300目 24×120目　';

SELECT
  DATALENGTH(@t) / 2 totalCharacters,
  LEN(@t) totalCharactersTrimmed,
  TRIM(@t) trimmedNormal,
  DATALENGTH(TRIM(@t)) / 2 totaTrimmedNormal,
  TRIM(NCHAR(0x3000) FROM @t) trimmedIdeographic,
  TRIM(N'　' FROM @t) trimmedIdeographic,
  DATALENGTH(TRIM(NCHAR(0x3000) FROM @t)) / 2 totalTrimmedIdeographic;
  
SELECT
  UNICODE(NCHAR(0x3000)) unicodeNum,
  ASCII(NCHAR(0x3000)) asciiNum;

db<>fiddle

You claim it has the same ASCII code, however that is just because ASCII does not have an exact character for it. If you use the UNICODE function, you will see the difference, as the fiddle shows.

For such characters as these, you must make sure to use the nvarchar data type, and the NCHAR and UNICODE functions.