Recognise repeating number in SQL Server

I have a SQL Server column of type int that stores testscores from 10 to 550, but some testscores do not fit the range, e.g. 521521.

What I want is to take the repeating part of the number and save that. How can I do this in SQL ?

• 521521 should become 521
• 919191 should become 91
• 7676 should become 76

I do not want to simply take the left (max) three spots or half of the string, as I also have data that says 123456, which of course should remain clear as unusable data. However, I am willing to take the risk for cases like 123123.

Therefore, I am really looking for something to recognise repeating numbers/letters.

You don't need string manipulation here. You can do it with some modulo and integer division, and a big CASE

SELECT *,
  Corrected = CASE
    WHEN col / 100    % 100    = col % 100    AND col / 10000       IN (0, col % 100   )
      THEN col % 100
    WHEN col / 1000   % 1000   = col % 1000   AND col / 1000000     IN (0, col % 1000  )
      THEN col % 1000
    WHEN col / 10000  % 10000  = col % 10000  AND col / 100000000   IN (0, col % 10000 )
      THEN col % 10000
    WHEN col / 100000 % 100000 = col % 100000 AND col / CAST(10000000000 AS bigint) IN (0, col % 100000)
      THEN col % 100000
    ELSE col
  END
FROM @Demo d;

db<>fiddle

Well, string manipulation isn't T-SQL's strong suit, but I was curious as to how hard will it be to find a pure T-SQL solution for this.
My solution is quite cumbersome but it works for all the scenarios you've described and a few scenarios I came up with based on your question. (BTW, with regards to Thom A's comment (expected result from 111111), it returns 111)

first, create and populate sample table (Please save us this step in your future questions):

DECLARE @Demo AS TABLE 
(
  col int,
  expected int
);

INSERT INTO @Demo  (col, expected) VALUES
(521521, 521), -- repeat after 3 digits
(919191, 91), -- repeat after 2 digits, 3 times
(919291, 919291), -- no repeat - Note the 92 in the middle
(7676, 76), -- repeat after 2 digits
(123456, 123456), -- no repeat
(2147421474, 21474), -- repeat after 5 digits 
(2147483647, 2147483647), -- no repeat
(12341234, 1234), -- repeat after 4 digits
(123123123,123), -- repeat after 3 digits, 3 times
(123456123,123456123); -- no repeat

Note the comments - they specify why the expected result is what it is.

Then, the query: I've had to use quite a few CROSS APPLYs and a CASE expression, but it does provide the expected result:

SELECT col, expected,
       CASE WHEN HasTwoParts = 1 
				AND LEFT(StringCol, NumberOfDigits/2) = RIGHT(StringCol, NumberOfDigits/2) THEN LEFT(StringCol, NumberOfDigits/2)
            WHEN HasThreeParts = 1 
				AND LEFT(StringCol, NumberOfDigits/3) = RIGHT(StringCol, NumberOfDigits/3) 
				AND LEFT(StringCol, NumberOfDigits/3) = SUBSTRING(StringCol, (NumberOfDigits/3)+1, (NumberOfDigits/3)) THEN LEFT(StringCol, NumberOfDigits/3)
			WHEN HasFiveParts = 1 
				AND LEFT(StringCol, NumberOfDigits/5) = RIGHT(StringCol, NumberOfDigits/5) THEN LEFT(StringCol, NumberOfDigits/5)
            ELSE StringCol
       END As Result
FROM @Demo 
CROSS APPLY (SELECT CAST(col as varchar(12)) As StringCol) As AsString
CROSS APPLY (SELECT LEN(StringCol) As NumberOfDigits) As Length
CROSS APPLY (
	SELECT IIF(NumberOfDigits % 2 = 0, 1, 0) As HasTwoParts, 
		   IIF(NumberOfDigits % 3 = 0, 1, 0) As HasThreeParts,
		   IIF(NumberOfDigits % 5 = 0, 1, 0) As HasFiveParts) As LengthDividers;

And the results:

col			expected	Result
------------------------------
521521		521			521
919191		91			91
919291		919291		919291
7676		76			76
123456		123456		123456
2147421474	21474		21474
2147483647	2147483647	2147483647
12341234	1234		1234
123123123	123			123
123456123	123456123	123456123

You can see a live demo on <kbd>db<>fiddle</kbd>

Here's another version:

DECLARE @data TABLE (n int, expected int)

INSERT INTO @data
VALUES	(521521, 521)
,	(919191, 91)
,	(7676, 76)
,	(123456, 123456)
,	(123123, 123)
,	(1234567, 1234567)
,	(111111111, 111)
,	(1111111111, 11111)
,	(1000011111, 1000011111)

SELECT	n, expected, actual
FROM	(

		SELECT	n, expected, actual
		,	ROW_NUMBER() OVER(PARTITION BY n
			ORDER BY CASE WHEN REPLICATE(actual, LEN(nString) / z.x) = nString THEN 1 ELSE 0 END DESC
			,	CASE WHEN actual = nString THEN 0 ELSE 1 END DESC
			,	LEN(actual) DESC
		) sort
		FROM	@data d
		CROSS APPLY (
				SELECT	CAST(n AS VARCHAR(12)) AS nString
			) n
		CROSS APPLY (
				SELECT	SUBSTRING(nString, 1, x.x) AS actual
				,	x.x
				FROM	(
					VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
				) x(x)
			) z
		) x
WHERE	x.sort = 1

What i do is take x number of characters from a string, and then trying to replicate these characters so they fill the string and trying to compare to the initial string. If it matches, it means string consist of repeating characters.

Finally, i sort them so we get most desired result, sorted by:

• repeated string being matched first
• but not non-repeating strings
• take the longest repeating string, this fixes problem with 11111111 strings, which technically repeat by "1", "11" and "1111"