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...
-
What's a training zero?Mike Marks– Mike Marks2014-03-11 19:18:47 +00:00Commented Mar 11, 2014 at 19:18
-
u mean trailing zeroes?KrazzyNefarious– KrazzyNefarious2014-03-11 19:19:37 +00:00Commented Mar 11, 2014 at 19:19
-
I'm sorry.. trailing zeros :-)Sal– Sal2014-03-11 19:20:20 +00:00Commented Mar 11, 2014 at 19:20
-
No examples seem to be working...Sal– Sal2014-03-11 20:17:58 +00:00Commented Mar 11, 2014 at 20:17
Add a comment
|
4 Answers
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.
Comments
Update table
set Dob = CONCAT(TRIM(Dob), '0')
where LEN(TRIM(Dob)) < 8
Comments
select cast(dob as nvarchar) + replicate('0',(8)-len(cast(dob as nvarchar)))
from table_name
2 Comments
Sal
this worked but just selected the data didn't update the data
KrazzyNefarious
update table_name set dob = cast(dob as nvarchar) + replicate('0',(8)-len(cast(dob as nvarchar)))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'
1 Comment
Sal
TRIM not recognized as a built in fi=unction in MS SQL 2008??