CODEWITHSUNDEEP
sqlt-sql
Sal
Sal

Reputation: 305

Adding trailing zeros to rows of data

I'd like to add trailing zeros to a data set however there is a WHERE clause involved. In a DOB field I have a date of 1971 and I'd like to add 0000 to make the length equal 8 characters. Sometimes there is 197108 which then I'd need to only add two 00. The fields that are null are ok. Any ideas?? Thanks in advance...

Upvotes: 0

Views: 780

Answers (4)

Mike Marks
Mike Marks

Reputation: 10139

Update table
    set Dob = CONCAT(TRIM(Dob), '0')
  where LEN(TRIM(Dob)) < 8

Upvotes: 0

Arjit
Arjit

Reputation: 3456

Try REPLICATE function.

REPLICATE('0',8-LEN(CAST(DOB AS NVARCHAR))) + CAST(DOB AS NVARCHAR)

Edit: Try this

select REPLICATE('0',8-LEN('1985')) + '1985'

Upvotes: 0

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

select cast(dob as nvarchar) + replicate('0',(8)-len(cast(dob as nvarchar)))
from table_name

SQL Fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You can add trailing zeros by doing:

select left(col+space(8), 8)

However, you probably shouldn't be storing date in a character field.

Upvotes: 1

Related Questions