Post reply

Not able to handle duplicates whild updation. Not as simple as you think..

  • October 30, 2011 at 1:16 am

    #247020

    I have a problem with a data correction procedure. The username / password policy of company is changed. They have introduced a minimum length and maximum length for username and passwords.

    tblUserAccounts table holds all username, password, email , etc. schema of which is given below.

    UserName varchar(35) unique,

    Password varchar(15),

    email varchar(20),

    custnum varchar(10)

    The stored procedure has the following input parameters

    1. @MinUserNameLen Int

    2. @MaxUserNameLen Int

    3. @MinPassLen Int

    4. @MaxPassLen Int

    5. @AdjustmentChar char(1)

    The rule is like :

    1. If the existing user name length is less than @MinUserNameLen, then we will append the adustment char that many times

    eg: @MinUserNameLen = 6 , @AdjustmentChar = '!'

    The user 'Kiran' will change to Kiran!

    2. If the username exceeds the @MaxUserNameLen, then the user name will get truncated

    eg : @MaxUserNameLen = 5, User Name = 'KiranKumar'

    The user name will change to Kiran

    3. It should not create duplicate records in the tblUserAccounts table. If the new name already exists in the tblUserAccounts table, it will renamed by appending 1,2,3... at the end.

    Eg: tblUserAccounts has the following data before SP updation

    User NamePassword email cust num

    VivekSahnis#5sfsfvivek.sahni@etc.in2323

    Praveen sw@#$praveen@etc.in 124

    Vivek P@ss vivek@etc.in 4214

    VivekPatilKis34#vivek.patil@etc.in 43432

    Input parameters are :

    1. @MinUserNameLen =3

    2. @MaxUserNameLen =5

    3. @MinPassLen = 3

    4. @MaxPassLen =5

    5. @AdjustmentChar ='!'

    Output would be like :

    User NamePassword email cust num

    Vive1 s#5sf vivek.sahni@etc.in2323

    Prave sw@#$praveen@etc.in 124

    Vivek P@ss!vivek@etc.in 4214

    Vive2 Kis34 vivek.patil@etc.in 43432

    You can see VivekSahni is truncated to Vivek. As it is already there in the table, it is appended with a number. To fit in the max length, it is renamed to vive1. Same for VivekPatil as well.

    We need to keep the log of the records changed in another table called tblUserPassChangeLog

    This will have the following fields

    OldUserName varchar(35)

    NewUserName varchar(35)

    OldPassword varchar(15)

    NewPassword varchar(15)

    I have created a procedure which is working fine in most of the conditions. But failing in where username comes like

    AAAAAAAAAAAAAAAAA

    AAAAAAAAAA

    AAAAAA

    Min len 4 and max len 16 kind of conditions.

    As this table has close to 8 lakh records in which more than 3 lakh falls into the invalid category.

    ie. We need to update almost 3 lakh records.

    Hope the scenario is clear. Could any one help ?

  • October 30, 2011 at 9:18 am

    #1400868

    You could use ROW_NUMBER() over the dupes to number them.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 30, 2011 at 9:41 am

    #1400869

    In the absence of any set up scripts, data and expected results. ...pls see code below...pure guess and certainly not optimised. It is not a full solution, but maybe give you some ideas.

    If you care to post some set up scripts, data and expected results...along with your current solution ..then I am sure that you will get some positive/tested answers

    USE [tempdb]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblUserAccounts]') AND type in (N'U'))

    DROP TABLE [dbo].[tblUserAccounts]

    GO

    CREATE TABLE [dbo].[tblUserAccounts](

    [UserName] [varchar](35) NULL,

    [custnum] [varchar](10) NULL

    ) ON [PRIMARY]

    INSERT INTO [dbo].[tblUserAccounts]([UserName], [custnum])

    SELECT N'AAAAAAAAAAAAAAAAAAAA', N'1' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAB', N'2' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAC', N'3' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAD', N'4' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAE', N'5' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAF', N'6' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAG', N'7' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAH', N'8' UNION ALL

    SELECT N'A', N'9' UNION ALL

    SELECT N'AA', N'10' UNION ALL

    SELECT N'AAA', N'11' UNION ALL

    SELECT N'AAAA', N'12' UNION ALL

    SELECT N'AAAAA', N'13' UNION ALL

    SELECT N'AAAAAA', N'14' UNION ALL

    SELECT N'AAAAAAA', N'15' UNION ALL

    SELECT N'AAAAAAAA', N'16' UNION ALL

    SELECT N'AAAAAAAAA', N'17' UNION ALL

    SELECT N'AAAAAAAAAA', N'18' UNION ALL

    SELECT N'AAAAAAAAAAA', N'19' UNION ALL

    SELECT N'AAAAAAAAAAAA', N'20' UNION ALL

    SELECT N'AAAAAAAAAAAAA', N'21' UNION ALL

    SELECT N'AAAAAAAAAAAAAA', N'22' UNION ALL

    SELECT N'AAAAAAAAAAAAAAA', N'23' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAA', N'24' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAA', N'25' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAA', N'26' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAA', N'27' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAI', N'28' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAJ', N'29' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAK', N'30' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAL', N'31' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAM', N'32'

    DECLARE @MinUserNameLen INT,

    @MaxUserNameLen INT,

    @AdjustmentChar CHAR(1)

    SET @MinUserNameLen = 8

    SET @MaxUserNameLen = 16

    SET @AdjustmentChar = '%'

    ;WITH cte

    AS (SELECT UserName,

    CASE

    WHEN LEN(USERNAME) < @MinUserNameLen

    THEN USERNAME + REPLICATE (@AdjustmentChar, @MinUserNameLen - LEN(USERNAME))

    WHEN LEN(USERNAME) > @MinUserNameLen

    THEN LEFT(USERNAME, @MinUserNameLen)

    ELSE USERNAME

    END AS X_user,

    ROW_NUMBER () OVER (PARTITION BY

    CASE

    WHEN LEN(USERNAME) < @MinUserNameLen

    THEN USERNAME + REPLICATE (@AdjustmentChar, @MinUserNameLen - LEN(USERNAME))

    WHEN LEN(USERNAME)> @MinUserNameLen

    THEN LEFT(USERNAME, @MinUserNameLen)

    ELSE USERNAME END ORDER BY USERNAME, CUSTNUM) AS RN,

    LEN (ROW_NUMBER () OVER (PARTITION BY

    CASE

    WHEN LEN(USERNAME) < @MinUserNameLen

    THEN USERNAME + REPLICATE (@AdjustmentChar, @MinUserNameLen - LEN(USERNAME))

    WHEN LEN(USERNAME)> @MinUserNameLen

    THEN LEFT(USERNAME, @MinUserNameLen)

    ELSE USERNAME END ORDER BY USERNAME, CUSTNUM)) AS LENRN

    FROM TBLUSERACCOUNTS)

    SELECT USERNAME,

    CASE

    WHEN RN > 1

    THEN ( LEFT(X_user, Len(X_user) - LENRN) ) + CAST(RN AS VARCHAR)

    ELSE X_user

    END AS NEWUSERNAME

    FROM CTE

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • October 30, 2011 at 10:19 am

    #1400871

    Thanks a lot brother. Let me experiment with this. This was great help 🙂

  • October 30, 2011 at 2:27 pm

    #1400892

    sanujss (10/30/2011)

    Thanks a lot brother. Let me experiment with this. This was great help 🙂

    can you show us what you got brother?

    my revised version here:

    USE [tempdb]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblUserAccounts]') AND type in (N'U'))

    DROP TABLE [dbo].[tblUserAccounts]

    GO

    CREATE TABLE [dbo].[tblUserAccounts](

    [UserName] [varchar](35) NULL,

    [custnum] [varchar](10) NULL

    ) ON [PRIMARY]

    INSERT INTO [dbo].[tblUserAccounts]([UserName], [custnum])

    SELECT N'AAAAAAAAAAAAAAAAAAAA', N'1' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAB', N'2' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAC', N'3' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAD', N'4' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAE', N'5' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAF', N'6' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAG', N'7' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAH', N'8' UNION ALL

    SELECT N'A', N'9' UNION ALL

    SELECT N'AA', N'10' UNION ALL

    SELECT N'AAA', N'11' UNION ALL

    SELECT N'AAAA', N'12' UNION ALL

    SELECT N'AAAAA', N'13' UNION ALL

    SELECT N'AAAAAA', N'14' UNION ALL

    SELECT N'AAAAAAA', N'15' UNION ALL

    SELECT N'AAAAAAAA', N'16' UNION ALL

    SELECT N'AAAAAAAAA', N'17' UNION ALL

    SELECT N'AAAAAAAAAA', N'18' UNION ALL

    SELECT N'AAAAAAAAAAA', N'19' UNION ALL

    SELECT N'AAAAAAAAAAAA', N'20' UNION ALL

    SELECT N'AAAAAAAAAAAAA', N'21' UNION ALL

    SELECT N'AAAAAAAAAAAAAA', N'22' UNION ALL

    SELECT N'AAAAAAAAAAAAAAA', N'23' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAA', N'24' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAA', N'25' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAA', N'26' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAA', N'27' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAI', N'28' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAJ', N'29' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAK', N'30' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAL', N'31' UNION ALL

    SELECT N'AAAAAAAAAAAAAAAAAAAM', N'32'

    DECLARE @MinUserNameLen INT,

    @MaxUserNameLen INT,

    @AdjustmentChar CHAR(1)

    SET @MinUserNameLen = 5

    SET @MaxUserNameLen = 16

    SET @AdjustmentChar = '%'

    ;WITH cte

    AS (SELECT UserName,

    CASE

    WHEN LEN(USERNAME) < @MinUserNameLen

    THEN USERNAME + REPLICATE (@AdjustmentChar, @MinUserNameLen - LEN(USERNAME))

    WHEN LEN(USERNAME) > @MinUserNameLen AND LEN(USERNAME) <= @maxUserNameLen

    THEN USERNAME

    WHEN LEN(USERNAME) > @MaxUserNameLen

    THEN LEFT(USERNAME, @MaxUserNameLen)

    ELSE USERNAME

    END AS X_user,

    ROW_NUMBER () OVER (PARTITION BY

    CASE

    WHEN LEN(USERNAME) < @MinUserNameLen

    THEN USERNAME + REPLICATE (@AdjustmentChar, @MinUserNameLen - LEN(USERNAME))

    WHEN LEN(USERNAME) > @MinUserNameLen AND LEN(USERNAME) <= @maxUserNameLen

    THEN USERNAME

    WHEN LEN(USERNAME) > @MaxUserNameLen

    THEN LEFT(USERNAME, @MaxUserNameLen)

    ELSE USERNAME

    END ORDER BY USERNAME, CUSTNUM) AS RN,

    LEN (ROW_NUMBER () OVER (PARTITION BY

    CASE

    WHEN LEN(USERNAME) < @MinUserNameLen

    THEN USERNAME + REPLICATE (@AdjustmentChar, @MinUserNameLen - LEN(USERNAME))

    WHEN LEN(USERNAME) > @MinUserNameLen AND LEN(USERNAME) <= @maxUserNameLen

    THEN USERNAME

    WHEN LEN(USERNAME) > @MaxUserNameLen

    THEN LEFT(USERNAME, @MaxUserNameLen)

    ELSE USERNAME

    END ORDER BY USERNAME, CUSTNUM)) AS LENRN

    FROM TBLUSERACCOUNTS)

    SELECT USERNAME,

    CASE

    WHEN RN > 1

    THEN ( LEFT(X_user, Len(X_user) - LENRN) ) + CAST(RN AS VARCHAR)

    ELSE X_user

    END AS NEWUSERNAME

    FROM CTE

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply