One more method to replace 6 consecutive digits from the input string. 🙂
--Create table and insert some data
CREATE TABLE DigitsToReplace
(
Chrs VARCHAR(1000)
)
GO
INSERT INTO DigitsToReplace VALUES ('1234ABC123456XYZ1234567890ADS')
INSERT INTO DigitsToReplace VALUES ('1234')
INSERT INTO DigitsToReplace VALUES ('123456')
INSERT INTO DigitsToReplace VALUES ('123456A!@#$%')
INSERT INTO DigitsToReplace VALUES ('')
INSERT INTO DigitsToReplace VALUES (' ')
INSERT INTO DigitsToReplace VALUES ('9876542345672a345673456734567')
INSERT INTO DigitsToReplace VALUES ('9876542345672345673456734567')
INSERT INTO DigitsToReplace VALUES ('a1AAAAAAAAAAAAAAAAAAAAAAA12345')
INSERT INTO DigitsToReplace VALUES ('AAAAAA')
--Create a Table Valued function
CREATE FUNCTION [dbo].[Replace6ContinousDigits]
(
@InputString VARCHAR(1000)
)
RETURNS @results TABLE
(
final VARCHAR(1000)
)
AS
BEGIN
DECLARE @Sttrs AS VARCHAR(1000) = @InputString
DECLARE @totals AS INT = 0
DECLARE @Counter AS INT = 1
SET @totals = DATALENGTH(@Sttrs)
DECLARE @Chrs TABLE (rnk TinyInt , Chars Char(1) , Digit TinyInt)
WHILE (@Counter <= @totals)
BEGIN
INSERT INTO @Chrs VALUES
(
@Counter
,SUBSTRING(@Sttrs,@Counter,1)
,CASE WHEN SUBSTRING(@Sttrs,@Counter,1) LIKE '[0-9]' THEN 1 ELSE @Counter END
)
SET @Counter = @Counter + 1
END
;WITH CTE AS
(
SELECT *,
CASE WHEN Digit = lag(Digit) OVER(ORDER BY rnk) THEN 0 ELSE 1 END cols
FROM @Chrs c2
)
,CTE1 AS
(
SELECT * , SUM(cols) OVER (ORDER BY rnk) grouper FROM CTE
)
,CTE2 AS
(
SELECT *, COUNT(*) OVER (PARTITION BY grouper) ConsecutiveDigits FROM CTE1
)
,CTE3 AS
(
SELECT chars, ConsecutiveDigits , ROW_NUMBER() OVER (PARTITION BY ConsecutiveDigits ORDER BY (SELECT NULL)) nkr
FROM CTE2 c
WHERE c.ConsecutiveDigits > 5
)
UPDATE c SET c.Chars = 'X'
FROM CTE3 c
DECLARE @final AS VARCHAR(1000) = ''
SELECT @final = @final + Chars FROM @Chrs
INSERT @results (final) SELECT @final
RETURN;
END
---Usage
SELECT Chrs InputString, final ReplacedString FROM DigitsToReplace
CROSS APPLY
(
SELECT final FROM [dbo].[Replace6ContinousDigits] (Chrs)
) A
Pawan Kumar Khowal
MSBISkills.com
Regards,
Pawan Kumar Khowal
MSBISkills.com