Is there a way to find default values that is a combination of the same number like 000/000/0 or 111-CodePudding

I want to find values in the SQL database that is a combination of the same number such as 0000 or 000/000/0 or 11111 or 99999 etc.

Is there a way to find these values without hardcoding?

What I am currently doing is:

select * from XXXX where value = '000/000/0'

CodePudding user response：

A simple solution is to remove all instances of first character of the string, and check if the result is an empty string:

select *
from t
where replace(replace(str, '/', ''), substring(str, 1, 1), '') = ''

CodePudding user response：

Try this on :

SELECT * 
FROM XXXX 
WHERE value IN ('000/000/0',11111,99999,0000)

If you need fill column values with application or other third-party. you can use stored procedure like below:

CREATE PROC dbo.usp_ListOfNumbers @NumberValues Nvarchar(200)
as
BEGIN
SELECT * 
FROM XXXX 
WHERE value = @NumberValues
END

for call just use

EXEC dbo.usp_ListOfNumbers @NumberValues = '000/000/0'