SQL Server Performance Forum – Threads Archive

add prefix 0 in a column value (zipcodes)

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

---

Try switching around your LEN statements. SQL server acts funny when reading the SUBSTRING function first. Also, what is your data type for Zipcode? If it isn’t a character type field, you will have to use either CAST or CONVERT to force it to that data type
SELECT DISTINCT ISNULL(P.id,”) PersonID, ISNULL(REPLACE(LTRIM(RTRIM(P.Firstname)),’,’,”),’ ‘) FirstName,
ZipCode = ISNULL(CASE WHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 3 THEN (’00’ + (PA.Zipcode))
WHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 4 THEN (‘0’ + (PA.Zipcode))
WHEN SUBSTRING(LTRIM(RTRIM(PA.Zipcode)),6,1) = ‘-‘ THEN ISNULL(PA.Zipcode,”)
WHEN SUBSTRING(PA.Zipcode,6,1) = ‘ ‘ THEN ISNULL(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 For a non-character datatype, try the following:
SELECT DISTINCT ISNULL(P.id,”) PersonID, ISNULL(REPLACE(LTRIM(RTRIM(P.Firstname)),’,’,”),’ ‘) FirstName,
ZipCode = ISNULL(CASE WHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 3 THEN (‘0’ + CONVERT(char(11), LTRIM((zipcode))))
WHEN LEN((LTRIM(RTRIM(PA.Zipcode)))) = 4 THEN (‘0’ + CONVERT(char(11), LTRIM((zipcode))))
WHEN SUBSTRING(LTRIM(RTRIM(PA.Zipcode)),6,1) = ‘-‘ THEN ISNULL(PA.Zipcode,”)
WHEN SUBSTRING(PA.Zipcode,6,1) = ‘ ‘ THEN ISNULL(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
Let me know if it works. – Tahsin

---

I think the first one should work fine as it is returning the correct data but i have to
check throughly as the data is huge one ,the data type for zipcode is varchar.
Thanks for your help

---

If you only want to add leading zero’s, what about:
DECLARE @zip VARCHAR(15), @places INT
SET @zip = ‘2814-1473’
SET @places = 5
SELECT REPLACE(STR(LEFT(@zip, CHARINDEX(‘-‘, @zip)-1) , @places), ‘ ‘, ‘0’)+’-‘+RIGHT(@zip, LEN(@zip)-CHARINDEX(‘-‘, @zip))
, RIGHT(LEFT(@zip, CHARINDEX(‘-‘, @zip)-1)+POWER(10, @places), @places)+’-‘+RIGHT(@zip, LEN(@zip)-CHARINDEX(‘-‘, @zip)) —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

Assuming that the second part has four digits number, this is simple DECLARE @zip VARCHAR(15), @places INT
SET @zip = ‘2814-1473’
select right(replicate(‘0’,10)[email protected],10)
Madhivanan Failing to plan is Planning to fail

---

Thanks Everyone
It worked out perfect

---

]]>