SQL Server Performance

add prefix 0 in a column value (zipcodes)

Discussion in 'T-SQL Performance Tuning for Developers' started by EasySQL, Feb 21, 2006.

  1. EasySQL New Member

    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
  2. Tahsin New Member

    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
  3. EasySQL New Member

    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
  4. FrankKalis Moderator

    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
  5. Madhivanan Moderator

    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)+@zip,10)


    Madhivanan

    Failing to plan is Planning to fail
  6. EasySQL New Member

    Thanks Everyone
    It worked out perfect

Share This Page