Question: Given the following - how can I replace **trailing spaces** with 0's in SQL Server?

```
CREATE TABLE TestTable
(
    TestField CHAR(8)
)   

INSERT INTO TestTable
SELECT ' 1 1 1  '   
INSERT INTO TestTable
SELECT ' 1 1 1 1'   

SELECT  
TestField,   
LEN(TestField) AS LenTestField,
REPLACE(TestField, ' ', '0') AS TestFieldReplaced,
LEN(REPLACE(TestField, ' ', '0')) AS LenTestFieldReplaced,
REPLACE(TestField, ' ', '0') + REPLICATE('0', DATALENGTH(TestField)-LEN(TestField)), 
REPLACE(CONVERT(VARCHAR,TestField), ' ', '0')
FROM    TestTable

```

Answer: 
You can leverage the difference in LEN and DATALENGTH. LEN removes trailing spaces,DATALENGTH doesn't. And REPLICATE will take a zero length parameter

```
SELECT
   RTRIM(TestField) + REPLICATE('0', DATALENGTH(TestField)-LEN(TestField))
FROM
   TestTable

```
how about this:

```
DECLARE @test TABLE
(num nvarchar(30) )

INSERT INTO @test (num) VALUES ('1 1 1   ')

SELECT LEFT(num, LEN(num)) + REPLICATE('0', LEN(REPLACE(num, ' ', '*')) - LEN(num)) FROM @test

```