How can I replace trailing spaces with 0's in SQL Server?

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

I updated this to include an example from the accepted answer, using replicate as indicated below. — Brandon
– Brandon, Commented Jun 21, 2011 at 16:34

gbn · Accepted Answer · 2011-06-21 15:31:10Z

3

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

answered Jun 21, 2011 at 15:31

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

devin · Accepted Answer · 2011-06-21 15:57:30Z

1

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

answered Jun 21, 2011 at 15:57

devin

1945 bronze badges

Collectives™ on Stack Overflow

How can I replace trailing spaces with 0's in SQL Server?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related