T-SQL Query | [ Replace 6 or more Consecutive Digits with x from a string Puzzle ]

Yesterday I was going through one of the puzzle asked in SQL Server Central website. The question is not simple 🙂 ; Here you have to replace all integers with continuous 6 or more occurrences with ‘X’. Less than 6 occurrences of integers should not be replaced. All other characters should not be touched. Let’s first go through the sample input and expected output below-

Pictorial presentation of the puzzle.(Pic taken from SQL Server Central website)

SQL Server Central Puzzle

Sample Input

Expected Output

Rules/Restrictions

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

--

--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')

--

Update Oct 5 | Solutions # – Pawan Kumar Khowal

--

--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 &amp;gt; 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

--

Thanks Guys ! Add a comment if you have any other solution in mind. We all need to learn. Keep Learning 🙂

Http://MSBISkills.com

Pawan Kumar Khowal