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 distinctisnull(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
]]>