How to get mask related with every part on same row ?

  • I work on SQL server 2012 I face issue I can't display Part Number with Mask related for family

    CREATE TABLE [dbo].[Partspc](
    [PortionKey] [nvarchar](255) NULL,
    [GroupID] [float] NULL,
    [familyid] [float] NULL
    ) ON [PRIMARY]

    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
    INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)


    CREATE TABLE [dbo].[Masksspc](
    [PortionKey] [nvarchar](255) NULL,
    [GroupID] [float] NULL,
    [familyid] [float] NULL
    ) ON [PRIMARY]


    INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
    INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
    INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
    INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
    INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
    INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'_', 4, 7524090)
    INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES ('blank', 4, 7524090)

    DECLARE @GetFinalResultParts as table
    ( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
    [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
    [PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
    )





    insert into @GetFinalResultParts
    SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
    FROM dbo.Partspc r with(nolock)


    ;WITH cte AS (
    SELECT t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber
    FROM @GetFinalResultParts t1
    WHERE t1.GroupID = 1
    UNION ALL
    SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber

    FROM @GetFinalResultParts t INNER JOIN
    cte s ON T.GroupID = s.GroupID + 1
    )
    SELECT familyid,PartNumber
    into getfinaldatapc
    from cte
    where groupid =(select max(GroupID) from dbo.Partspc with(nolock))
    group by familyid,PartNumber


    DECLARE @GetFinalMasks as table
    ( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
    [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
    [MaskNumber] [nvarchar](200) NOT NULL INDEX IXkmask NONCLUSTERED)





    insert into @GetFinalMasks
    SELECT distinct m.familyid,m.GroupID,IIF(m.PortionKey='blank','',m.PortionKey)
    FROM dbo.Masksspc m with(nolock)


    ;WITH cte AS (
    SELECT t1.familyid,t1.GroupID,cast((t1.MaskNumber) as nvarchar(200)) MaskNumber
    FROM @GetFinalMasks t1
    WHERE t1.GroupID = 1
    UNION ALL
    SELECT t.familyid,t.GroupID,cast((s.MaskNumber+t.MaskNumber) as nvarchar(200)) MaskNumber

    FROM @GetFinalMasks t INNER JOIN
    cte s ON T.GroupID = s.GroupID + 1
    )
    SELECT familyid,MaskNumber
    into getfinaldatapcmask
    from cte
    where groupid =(select max(GroupID) from dbo.Masksspc with(nolock))
    group by familyid,MaskNumber

    SELECT * FROM getfinaldatapc
    select * from getfinaldatapcmask

     

    expected result as below :

    familyid    PartNumber    MaskNumber
    7524090 T496B754 T496B754
    7524090 T496B754K T496B754_
    7524090 T496B754M T496B754_
    7524090 T496B755 T496B755
    7524090 T496B755K T496B755_
    7524090 T496B755M T496B755_
    7524090 T496X754 T496X754
    7524090 T496X754K T496X754_
    7524090 T496X754M T496X754_
    7524090 T496X755 T496X755
    7524090 T496X755K T496X755_
    7524090 T496X755M T496X755_

     

    mask and part must be equal on length

    blank converted to space

    on group 4 for part have one character will be underscore _ on mask

    so k on part will be underscore _ on mask

    m on part will be underscore _ on mask

    so please how to display part number with mask related

  • Does this work:

    SELECT
    [finalDatapc].[familyid]
    , [finalDatapc].[PartNumber]
    , [finalDatapcMask].[MaskNumber]
    FROM [getfinaldatapc] AS [finalDatapc]
    JOIN [getfinaldatapcmask] AS [finalDatapcMask]
    ON [finalDatapcMask].[familyid] = [finalDatapc].[familyid]
    AND
    (
    LEFT([finalDatapc].[PartNumber], LEN([finalDatapc].[PartNumber]) - 1) = LEFT([finalDatapcMask].[MaskNumber], LEN([finalDatapcMask].[MaskNumber]) - 1)
    AND RIGHT([finalDatapc].[PartNumber], 1)NOT LIKE '[0-9]'
    )
    OR ([finalDatapc].[PartNumber] = [finalDatapcMask].[MaskNumber]);

    Basically JOINing the 2 tables on the Family ID matching and then IF you remove the last character from both strings, the strings match AND the last character must not be numeric (ie ends in a K or M) OR if the 2 values match.

    I see 12 results in your data set above and 12 in mine, so I think this approach works given the sample data, but will break as soon as the K or M becomes a number OR if the K or M is more than 1 character in length.  There are limitations on what I provided but it works with the sample data.

  • thank you for reply

    are there are another solution dynamic for that

    on my case there are partnumber and masknumber like below

     

    familyid    PartNumber   MaskNumber
    7524090  T496B754Kgfd    T496B754____

    also I ask if there are another solution join part and mask by id

    or that is difficult

  • can i generate unique id for table getfinaldatapc by checksum or what every

    and generate unique id for table getfinaldatapcmask also by checksum

    then join between two tables by id generated

    if this possible can you help me applying

  • It is definitely possible, but does become more challenging based on what you have in your original query.  What would probably be easier would be something more like this:

    DECLARE @MaskChar CHAR(1) = '_'

    CREATE TABLE #partspc(
    [PortionKey] [nvarchar](255) NULL,
    [GroupID] [float] NULL,
    [familyid] [float] NULL
    ) ON [PRIMARY]

    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)

    DECLARE @GetFinalResultParts as table
    ( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
    [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
    [PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
    )





    insert into @GetFinalResultParts
    SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
    FROM #Partspc r with(nolock)

    DECLARE @FinalData TABLE (familyID FLOAT, PartNumber NVARCHAR(255), lenLast INT)

    ;WITH cte AS (
    SELECT t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber, 1 AS lenLast
    FROM @GetFinalResultParts t1
    WHERE t1.GroupID = 1
    UNION ALL
    SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber, LEN(.[PartNumber]) AS lenLast

    FROM @GetFinalResultParts t INNER JOIN
    cte s ON T.GroupID = s.GroupID + 1
    )
    INSERT INTO @FinalData ([familyID], [PartNumber], [lenLast])
    SELECT familyid,PartNumber, lenLast
    from cte
    where groupid =(select max(GroupID) from #Partspc)

    SELECT familyID, PArtNumber, CASE WHEN lenLast = LEN(partnumber) THEN partnumber ELSE LEFT(partnumber,lenLast) + REPLICATE(@MaskChar,LEN(partnumber)-lenlast) end
    FROM @finalData

    DROP TABLE #partspc

    NOW, the above works by assigning that mask character to a variable at the start rather than building things up like you were.  If building it up is a requirement (such as that the mask MAY occur on characters other than the last set OR that multiple masks may need to be applied in some cases), the above would need to be tweaked.

    Basically what I ended up doing was grabbing the last length of partnumber which would be the length of it PRIOR to it having the last chunk added on.  At this point, we have all of the data we need to mask things as you indicated.

    Now, does the above work OR do you need it to work with the tables you had?  If so, it is possible, and isn't that much more challenging UNLESS you have multiple mask values you need to apply (such as _ and *).  Again, not impossible, but definitely more challenging.

    • This reply was modified 1 day, 7 hours ago by  Mr. Brian Gale. Reason: Removed NOLOCK as I don't think that hint is actually required
  • thank you for reply

    what benefit from above

    familyID	PArtNumber	(No column name)
    7524090 T496X755 ________
    7524090 T496X755K T________
    7524090 T496X755M T________
    7524090 T496X754 ________
    7524090 T496X754K T________
    7524090 T496X754M T________
    7524090 T496B755 ________
    7524090 T496B755K T________
    7524090 T496B755M T________
    7524090 T496B754 ________
    7524090 T496B754K T________
    7524090 T496B754M T________

    why result ended with underscore

    can you please tell me what idea from that

  • Sorry, my query had a typo in it - I missed giving the quantifier for the part number in the CTE.

    This one should work better:

    DECLARE @MaskChar CHAR(1) = '_'

    CREATE TABLE #partspc(
    [PortionKey] [nvarchar](255) NULL,
    [GroupID] [float] NULL,
    [familyid] [float] NULL
    ) ON [PRIMARY]

    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
    INSERT #partspc ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)

    DECLARE @GetFinalResultParts as table
    ( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
    [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
    [PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
    )





    insert into @GetFinalResultParts
    SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
    FROM #Partspc r with(nolock)

    DECLARE @FinalData TABLE (familyID FLOAT, PartNumber NVARCHAR(255), lenLast INT)

    ;WITH cte AS (
    SELECT t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber, 1 AS lenLast
    FROM @GetFinalResultParts t1
    WHERE t1.GroupID = 1
    UNION ALL
    SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber, LEN(.[PartNumber]) AS lenLast

    FROM @GetFinalResultParts t INNER JOIN
    cte s ON T.GroupID = s.GroupID + 1
    )
    INSERT INTO @FinalData ([familyID], [PartNumber], [lenLast])
    SELECT familyid,PartNumber, lenLast
    from cte
    where groupid =(select max(GroupID) from #Partspc)

    SELECT familyID, PartNumber, CASE WHEN lenLast = LEN(partnumber) THEN partnumber ELSE LEFT(partnumber,lenLast) + REPLICATE(@MaskChar,LEN(partnumber)-lenlast) END AS [MaskedPartNumber]
    FROM @finalData

    DROP TABLE #partspc

    I also did a mistake that I hate - I didn't give the last column a name.  I fixed that too.

  • thank you for reply

    what i need to get part with related mask

    so until now i not give my part number any unique number

    and give mask number same unique number i give to part number

    then join it by that unique number

     

  • As for generating a checksum, this is entirely possible, but it depends on how you want to handle it and what you plan to do with it.

    If I understand the requirement correctly, you are wanting to go from a masked value back to a real value. This is definitely possible, but you will run into a snag.  Using the original data set, lets say the end user sees:

    T496B754_

    When you go back to the database to try to determine what ID to use for that, you will have 2 values - one for T496B754M and one for T496B754K.  Which one will you choose?  If end users need to pick one based on a checksum, they will have no idea which one to pick as it'll just be a string of letters and numbers.

    As for how you would go about calculating that, my approach would be to use some form of repeatable checksum generation such as MD5.  Build the MD5 on the part number and as long as all part numbers are unique, then the MD5 should be unique too.  Then present the MD5 along with the masked part number and you can always go back to the original part number by re-calculating the MD5 and matching it up.  You don't even need to store that MD5 unless you have a LOT of data as calculating an MD5 is pretty quick, but will be slow if you have millions or billions of rows of data.

    Alternately, you could use an ID value that is an INT (or bigint) and set it up as an IDENTITY(1,1) so it is ever increasing and then present the ID along with the masked value to the end users.

  • can you help me

    applying that please

  • It depends entirely on the approach you want to take and how you are planning on storing the data permanently.

    Is this data going into a table or is it generated on the fly for the end user?

    If it is going into a table, I'd just add a column onto the table with a name like PartNumberID of type INT IDENTITY(1,1).  That'll be the easiest solution that will ensure row uniqueness.  But you will still run into the problem that part number T496B754_ matches up with both T496B754M and T496B754K, although those SHOULD have a unique ID.

    But it depends heavily on what you plan to use that value for and how you are planning on storing your data.

  • i need to use that below

    Alternately, you could use an ID value that is an INT (or bigint) and set it up as an IDENTITY(1,1) so it is ever increasing and then present the ID along with the masked value to the end users.

    but i don't know how to apply it

  • That approach works if you are storing it in a table.  Just add that as a column to the table.

Viewing 13 posts - 1 through 13 (of 13 total)

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