How to update status based on positional value of another column

Transact-SQL
,
1

problem

How to update status with wrong length where length after $ on Signature Key not equal length of portion Key depend on groupid ?

I work on SQL server 2012 I face Issue I can't Update Status with 'wrong length message' where length of portion key

not equal length of signature key after $ based on group Id ?

as Example Signature Key ******

create table #Ref
(
SignatureKey  nvarchar(50),
GroupId int,
PortionKey nvarchar(50),
Status nvarchar(100)
)
insert into #Ref(SignatureKey,GroupId,PortionKey,status)
values
('*$*$**$***$**$**$*',3,'s',NUll),
('*$*$*$***$*$**$*',4,'s2',NUll),
('*$*$*$***$*$**$*',6,'shd',NUll),
('*$**$*$***$***$**$*',2,'g',NUll),
('*$**$*$***$**$**$*',5,'f',NUll)

Expected result will be:

 SignatureKey	GroupId	PortionKey	Status
*$*$**$***$**$**$*	3	s	wrong length
*$*$*$***$*$**$*	4	s2	wrong length
*$*$*$***$*$**$*	6	shd	wrong length
*$**$*$***$***$**$*	2	g	wrong length
*$**$*$***$**$**$*	5	f	wrong length

as above first signature key will be status wrong length because Group Id 3 after second $ have length 2 but portion key s

length is 1 because it only character and 1 not equal 2 so length will be wrong length .

image
image929×353 5.33 KB

2

hi

i tried to do this !!! hope this is correct !! please check !!!
for the sake of testing i changed groupid 3 portionkey to ss

please click arrow to the left for drop create data script 
create table Ref
(
SignatureKey  nvarchar(50),
GroupId int,
PortionKey nvarchar(50),
Status nvarchar(100)
)

insert into Ref(SignatureKey,GroupId,PortionKey,status)
values
('******',3,'ss',NUll),
('******',4,'s2',NUll),
('******',6,'shd',NUll),
('*******',2,'g',NUll),
('**********',5,'f',NUll)

; with cte as 
(
	select row_number() over(order by Groupid) as rn ,*,len(PortionKey) as ln_pk from ref 
) , cte_nxt as 
(
	select row_number() over(partition by SignatureKey order by (select null)) as rn_pK, * from cte cross apply  STRING_SPLIT(cte.SignatureKey,'$')
) 
select 
      'SQL Output' 
   , a.SignatureKey
   , a.GroupId
   , a.PortionKey
   , case when a.ln_pk = len(b.value) then 'Correct Length Message' else 'Wrong Length Message' end as Status 	 
from 
    cte a 
	   join 
	cte_nxt b 
	   on a.SignatureKey = b.SignatureKey 
	         and 
		  a.GroupId = b.rn_pK
order by 
   a.GroupId

image

image

3

thanks for reply
I get error Invalid object name 'STRING_SPLIT'
I work on SQL server 2012

4

hi

ok

you can use Jeff Modens Delimited 8k Split Function ...

please click arrow to the left for the function .. 
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

; with cte as 
(
	select row_number() over(order by Groupid) as rn ,*,len(PortionKey) as ln_pk from ref 
) , cte_nxt as 
(
	select row_number() over(partition by SignatureKey order by (select null)) as rn_pK, * from cte cross apply  [dbo].[DelimitedSplit8K](cte.SignatureKey,'$')
) 
select 
      'SQL Output' 
   , a.SignatureKey
   , a.GroupId
   , a.PortionKey
   , case when a.ln_pk = len(b.Item) then 'Correct Length Message' else 'Wrong Length Message' end as Status 	 
from 
    cte a 
	   join 
	cte_nxt b 
	   on a.SignatureKey = b.SignatureKey 
	         and 
		  a.GroupId = b.rn_pK
order by 
   a.GroupId
go

image

5

using @JeffModen DelimitedSplit8K

use sqlteam
go

create table #Ref
(
SignatureKey  nvarchar(50),
GroupId int,
PortionKey nvarchar(50),
Status nvarchar(100)
)
insert into #Ref(SignatureKey,GroupId,PortionKey,status)
values
('*$*$**$***$**$**$*',3,'s',NUll),
('*$*$*$***$*$**$*',4,'s2',NUll),
('*$*$*$***$*$**$*',6,'shd',NUll),
('*$**$*$***$***$**$*',2,'g',NUll),
('*$**$*$***$**$**$*',5,'f',NUll),
('*$*$*******$*$**$**$*',7,'f',NUll) --test extra to see accuracy 7

;with jazz
as
(
	select * 
	From #Ref r
	cross apply DelimitedSplit8K(r.SignatureKey, '$')
--this function from Sir Jeff Moden
--https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function
)
select SignatureKey ,GroupId, PortionKey,
       case 
	      when len(item) = GroupId then 'Correct Result'
		  else 'Wrong length' 
		  end as Status
 from jazz
 where ItemNumber = 3
drop table #Ref

image

6

thank you very much