I have the following table:
I need to update the ActivityCode column like this:
- if the third sequence of numbers ( by splitting it with
.as separator ) is between 1 and 24, then add the sequence02before it. Example 1st row should be:110.153.02.11 - if the third sequence of numbers ( by splitting it with
.as separator ) is between 25 and 49, then add the sequence01before it, but for the last sequence start the count from 1. Example 4th row should be:110.153.01.01
More examples:
110.153.26 --> 110.153.01.02
110.153.27 --> 110.153.01.03
I tried something like this so far, but have zero idea how to continue:
WITH cte
AS (SELECT WorkActivityTypeId,
ActivityCode,
CASE
WHEN LEFT(SUBSTRING([ActivityCode],CHARINDEX('.', [ActivityCode]) 1, LEN([ActivityCode]) - CHARINDEX('.', [ActivityCode]) ),3) <> '000'
THEN
???
ELSE ActivityCode
END NewActivityCode
FROM [VanBerloERP_Acc].[dbo].[WorkActivityTypesTest])
UPDATE cte
SET ActivityCode = NewActivityCode;
CodePudding user response：
select
code
,CASE
WHEN CONVERT(int,RIGHT(code,2)) BETWEEN 1 AND 24 THEN STUFF(code,8,0,'.02')
WHEN CONVERT(int,RIGHT(code,2)) BETWEEN 25 AND 49 THEN LEFT(STUFF(code,8,0,'.01'),11) LastSeq
ELSE code
ENd
from (
SELECT
*
,LastSeq = right('00' CONVERT(varchar(10),CONVERT(int,RIGHT(code,2))-25 1),2)
FROM
(
values
('110.153.26')
,('110.153.25')
,('110.153.27')
,('110.153.11')
,('110.153.49')
) N(code)
) T
CodePudding user response：
For cases when the right 2 digits are 1 through 24, you just need to stuff '.02' into the string.
For cases when the right 2 digits are 25 through 49, you need to add '.01' to the first pair, and then subtract 24 from the last number and re-append it. This involves a bit of converting back and forth and it looks messy the way I did it. Should work though.
I've assumed that the first 2 numbers are always 3 digits and the last two numbers are always 2 digits. If that isn't the case, then this gets messier.
Finally, as a precaution, if the number isn't expected, it just writes the original number back. This could also be avoided by a judicious WHERE clause.
This may not be the best way, but it should work.
UPDATE
[VanBerloERP_Acc].[dbo].[WorkActivityTypesTest]
SET
ActivityCode =
CASE WHEN TRY_PARSE( SUBSTRING( ActivityCode, 9, 2 ) AS INT )
BETWEEN 1 AND 24
THEN STUFF( ActivityCode, 8, 0, '.02' )
WHEN TRY_PARSE( SUBSTRING( ActivityCode, 9, 2 ) AS INT )
BETWEEN 25 AND 49
THEN CONCAT(
SUBSTRING( ActivityCode, 1, 7 )
,'.01.'
,RIGHT(
CONCAT(
'00'
,LTRIM(
STR(
TRY_PARSE( RIGHT( ActivityCode, 2 ) AS INT ) - 24
,2
,0
)
)
)
,2
)
)
ELSE ActivityCode
END
;
CodePudding user response：
If this answer is bad form, please direct me to the applicable StackOverflow rule and I'll take this answer down.
Out of curiosity, I wanted to see how this would look with a MERGE instead of an update. It doesn't use the OP's table, but the code below runs and produces correct results for the sample set.
It is clearly not the most efficient or even a recommended way to solve this problem, but it was a fun exercise.
Here's the MERGE solution I came up with:
BEGIN
DECLARE @dottedSet TABLE ( ValueCode VARCHAR(25) );
INSERT
INTO
@dottedSet
VALUES
('110.153.11')
,('110.117.09')
,('110.153.05')
,('110.119.25')
,('110.153.18')
,('110.113.13')
,('110.150.12')
,('110.152.22')
,('110.153.20')
;
MERGE
INTO
@dottedSet DS
USING
(
SELECT
SPLIT.ValueCode
,CONCAT(
SPLIT.PREFIX
,SPLIT.INFIX
,REVERSE( SUBSTRING( REVERSE( CONCAT( '00', SPLIT.SUFFIX ) ), 1, 2 ) )
) AS 'NEW_CODE'
FROM
(
SELECT
DS2.ValueCode
,SUBSTRING( DS2.ValueCode, 1, 8 ) AS 'PREFIX'
,CASE WHEN TRY_PARSE( SUBSTRING( DS2.ValueCode, 9, 2 ) AS INT )
BETWEEN 1 AND 24
THEN '02.'
WHEN TRY_PARSE( SUBSTRING( DS2.ValueCode, 9, 2 ) AS INT )
BETWEEN 25 AND 49
THEN '01.'
ELSE ''
END AS 'INFIX'
,CASE WHEN TRY_PARSE( SUBSTRING( DS2.ValueCode, 9, 2 ) AS INT )
BETWEEN 25 AND 49
THEN LTRIM( STR( TRY_PARSE( SUBSTRING( DS2.ValueCode, 9, 2 ) AS INT ) - 24, 2, 0 ) )
ELSE LTRIM( STR( TRY_PARSE( SUBSTRING( DS2.ValueCode, 9, 2 ) AS INT ), 2, 0 ) )
END AS 'SUFFIX'
FROM
@dottedSet DS2
) SPLIT
) NEW_DS
ON DS.ValueCode = NEW_DS.ValueCode
WHEN MATCHED
THEN UPDATE
SET
DS.ValueCode = NEW_DS.NEW_CODE
OUTPUT
DELETED.ValueCode AS 'OLD_VALUE_CODE'
,INSERTED.ValueCode AS 'NEW_VALUE_CODE'
;
END
The above produces:
---------------- ----------------
| OLD_VALUE_CODE | NEW_VALUE_CODE |
---------------- ----------------
| 110.153.11 | 110.153.02.11 |
| 110.117.09 | 110.117.02.09 |
| 110.153.05 | 110.153.02.05 |
| 110.119.25 | 110.119.01.01 |
| 110.153.18 | 110.153.02.18 |
| 110.113.13 | 110.113.02.13 |
| 110.150.12 | 110.150.02.12 |
| 110.152.22 | 110.152.02.22 |
| 110.153.20 | 110.153.02.20 |
---------------- ----------------