How to find duplicate characters in a string in SQL Server?

How to find duplicate characters in a string in SQL Server?

Below is the query that you can use in-order to get a character count from a string:

--Declared a table variable that will act as a temp table to hold the values

DECLARE @tbl_char TABLE (input VARCHAR(100),letter VARCHAR(1),remainder VARCHAR(100))

--SET the input & the character you want to search

DECLARE @Input VARCHAR(100)='suryapratapsingh', @characterToFind VARCHAR(1)='a', @remainder VARCHAR(100), @letter VARCHAR(100)=''
SET @remainder=@Input

--WHILE loop to iterate through all the remainder values of the string

WHILE(LEN(@remainder)>0)
BEGIN
  SELECT @Input=@Input, @letter=CONVERT(VARCHAR(1),LEFT(@remainder,1)), @remainder= RIGHT(@remainder, LEN(@remainder)-1)

  INSERT INTO @tbl_char(input,letter,remainder) VALUES (@Input,@letter,@remainder)
END

--Getting the count of the letter required by using a group by clause

SELECT Letter,count(1) AS 'Repeated_no_of_times' FROM @tbl_char
WHERE letter=@characterToFind
GROUP BY letter


Below is the output that we receive when we pass the inputstring=’suryapratapsingh’ and character_to_search=’a’:

findDuplicate characters in string Sql Server





You may also like

No comments:

If you have any queries regarding the article or in performing something in Microsoft SQL Server then please let me know.