How to trim empty spaces in SQL Insert Query?

Question

Below query which insert records to the Table does not trim the empty space.

Insert into MyTable (MyColumn)
select RTRIM(LTRIM(rowvalue)) from #temptable

Alternatively, if i assign to a variable and insert back, empty spaces are trimmed.

Declare @var varchar(max)
set @var = (    select RTRIM(LTRIM(rowvalue)) from #temptable  )
go
Insert into MyTable (MyColumn) values ( @var)

Instead of assigning, is there a way to trim the empty spaces in the Insert query.

Your original insert statement absolutely will remove leading and trailing spaces. — Sean Lange, Feb 13 '19 at 17:49
Your second approach will fail if `subquery` return more than one row. — Yogesh Sharma, Feb 13 '19 at 17:49
@SeanLange , Original insert query is not trimming the empty space — goofyui, Feb 13 '19 at 17:50
Does `CAST(RTRIM(LTRIM(rowvalue)) AS VARCHAR(max))` work in your first? What is the column type of `rowvalue` in your temp table? — JNevill, Feb 13 '19 at 17:50
It absolutely will remove leading and trailing spaces. Maybe you have characters that are not spaces? — Sean Lange, Feb 13 '19 at 17:51
just note tsql has `TRIM` function which is analogue of `rtrim(ltrim(...))` — Iłya Bursov, Feb 13 '19 at 17:52
If it's not trimming the spaces it may be that you have high ASCII characters in the field. — Element Zero, Feb 13 '19 at 17:53
@SeanLange , that could be possible .. of having character which is not visbile.. such as dot or something ..else — goofyui, Feb 13 '19 at 17:53

score 0 · Answer 1 · answered Feb 13 '19 at 17:57

It may be that you have high ASCII characters in the field. Try this and see if the result comes back, it checks whether the field has characters between 1-31 and 127-255.

-- See if it's in the low ASCII range
SELECT *      
FROM #temp      
WHERE rowvalue LIKE '%[' + CHAR(1)+ '-' +CHAR(31)+']%'  COLLATE Latin1_General_100_BIN2

-- See if it's in the high ASCII range
SELECT *      
FROM #temp      
WHERE rowvalue LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2

If the record comes back you may need to replace the character in order to remove it. See here

How to trim empty spaces in SQL Insert Query?

1 Answers1