add prefixes 0 in a column value (zipcodes)

[ezsql]

For zipcodes where the values is not null and less than 3 i have to add two 0's,for zipcodes where value is 4 digit i have to add one 0.this is in a case statement and my zip code format is "02814-1473" if it is ''342"then it should be "00342" if it is "1342" then it should be "01432" in some cases there are 10 digits but i have to look for the one on left hand side of the hiphen.

here is my code :
SELECT distinct
isnull(P.id,'') PersonID
, isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),' ') FirstName
, ZipCode = isnull(case when substring(Ltrim(Rtrim(PA.Zipcode)),6,1) = '-' then isnull(PA.Zipcode,'')
when substring(PA.Zipcode,6,1) = ' ' then isnull(PA.Zipcode,'')
when len((Ltrim(Rtrim(PA.Zipcode)))) = 3 then ('0' + '0' + (PA.Zipcode))
when len((Ltrim(Rtrim(PA.Zipcode)))) = 4 then ('0' + (PA.Zipcode))
else substring(PA.Zipcode,1,5)+'-'+ substring(PA.Zipcode,6,4)
end ,'')
FROM vwPersonPrefAddr pa with(nolock), vwpersons p with (nolock)
where p.id = pa.id

[skhanal]

Easiest way is to always pad left with 5 or 10 0s and get 5 or 10 characters from right

select
case
when charindex('-',zip) = 0 then right('00000'+zip),5)
else select right('0000000000'+zip),10)
end

[ezsql]

Yeh but my intension is to add single zero or two zero for zipcodes like
'1466-5406' or '466-5406'
and in some cases the zipcode is only 1466 or 466 .
I hope its clear .
Thanks for quick response

[nosepicker]

skhanal's query will work for what you want to do, although the syntax needs to be cleaned up a little bit:

select
case
when charindex('-',zip) = 0 then right(('00000'+zip),5)
else right(('0000000000'+zip),10)
end

[mikr0s]

ZipCode = ISNULL(REPLICATE('0', 5-LEN(SUBSTRING(PA.ZipCode, 1, CASE CHARINDEX('-', PA.ZipCode, 1) WHEN 0 THEN LEN(PA.ZipCode) ELSE CHARINDEX('-', PA.ZipCode, 1)-1 END )) ), '') + PA.ZipCode


HTH

[nosepicker]

That's supposed to be an improvement?

[mikr0s]

DECLARE @zip NVARCHAR(10)

SELECT @zip = '1234-'

select case when charindex('-',@zip) = 0 then right(('00000'+@zip),5)
else right(('0000000000'+@zip),10)
end

in some cases there are 10 digits but i have to look for the one on left hand side of the hiphen

@zip should give '01234-', instead of '000001234-'

HTH

[nosepicker]

Yeh but my intension is to add single zero or two zero for zipcodes like
'1466-5406' or '466-5406'
and in some cases the zipcode is only 1466 or 466 .

He didn't mention anything about a case like '1234-'. "Left side of the hyphen" implies not including the hyphen.

[mikr0s]

I guess nosepicker is right....
The point is the flexibility of the code no matter the format of data...
The behaviour of the two options(codes) is different especially on zip codes of type xxx-xx (less than 4 digits after the hyphen), xxxxxx (more than 5 digits, for any reason).

IN case of an xxxxxx zipcode, the user in listing, sees only 5digits of the zipcode (using the right.....option), no matter if in the db there are 6digits...In a way it is about data integrity too...

HTH