Sql to get characters repeat

Hi ,
  In Sql server 2016 , I need the Sql query check for a table field  if a character repeats more than once in a given value
    For example, in the following sample rows for VersionNo
     1.1.1
     1.1
      2
     2.1.1.2
     2.3
     As result of query , I should get
       1.1.1
        2.1.1.2

    Thanks
Sam OZAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Bill, that would need to be '%.%.%'
1
 
Bill PrewCommented:
Why wasn't "1.1" also selected as a valid result, it has two repeating "1"s?


»bp
1
 
Bill PrewCommented:
Here's a somewhat brute force approach, not too elegant but should get the job done.

SELECT * 
  FROM Table1
 WHERE REPLACE(VersionNo, '.', '') LIKE '%00%'
    OR REPLACE(VersionNo, '.', '') LIKE '%11%'
    OR REPLACE(VersionNo, '.', '') LIKE '%22%'
    OR REPLACE(VersionNo, '.', '') LIKE '%33%'
    OR REPLACE(VersionNo, '.', '') LIKE '%44%'
    OR REPLACE(VersionNo, '.', '') LIKE '%55%'
    OR REPLACE(VersionNo, '.', '') LIKE '%66%'
    OR REPLACE(VersionNo, '.', '') LIKE '%77%'
    OR REPLACE(VersionNo, '.', '') LIKE '%88%'
    OR REPLACE(VersionNo, '.', '') LIKE '%99%';

Open in new window


»bp
1
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Sam OZAuthor Commented:
My apologies. I did not make it clear -   I need only  repetition of  "." captured . Not any other characters
0
 
Mark WillsTopic AdvisorCommented:
Are they only ever numerals ?
Do you know what character to check for ?
Do you know what shouldnt be checked for ?

We could do something generic for any character...
WITH CTE_Nums AS
( SELECT number 
  FROM master.dbo.spt_values WHERE type = 'P'
),CTE_Strings AS
( SELECT S.YourColumn, IIF (SUBSTRING(S.YourColumn,n.Number,1) not in (' ','.'), CHARINDEX(SUBSTRING(S.YourColumn,n.Number,1),S.YourColumn,n.Number+1),0) recurs   
  FROM #Your_Table S
  JOIN CTE_Nums n ON n.Number > 1 and n.Number < LEN(S.YourColumn) 
) SELECT DISTINCT YourColumn
  FROM CTE_Strings where recurs > 0 

Open in new window


Or if checking a specific character then much easier...
SELECT YourColumn,(LEN(YourColumn) - LEN(REPLACE(YourColumn, '.', ''))) CountChars
FROM #Your_Table
WHERE (LEN(YourColumn) - LEN(REPLACE(YourColumn, '.', ''))) > 0

Open in new window

0
 
Bill PrewConnect With a Mentor Commented:
Okay, then try.

SELECT * 
  FROM Table1
 WHERE VersionNo LIKE '%..%';

Open in new window


»bp
0
 
Bill PrewCommented:
Not sure I understand, I read:
My apologies. I did not make it clear -   I need only  repetition of  "." captured . Not any other characters
And interpreted that to mean they were looking for multiple contiguous periods, did I miss something?


»bp
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Waiting for a better explanation of the problem.
0
 
Mark WillsTopic AdvisorCommented:
@bill, my interpretation was "How many times does a fullstop character (ie '.') appear in a string, and show me the string when more than 1"

Hence SELECT YourColumn FROM YourTable WHERE YourColumn LIKE '%.%.%'

Will give the results of 1.1.1 and 2.1.1.2  (having 2 and 3 dots respectively)

Although, I did really like my generic "any character" scan (back in #a42428634 ), I do think the wildcard search is the easiest to understand and do....

That assumes of course that we now understand the requirement correctly :) and given Vitor's comment above, there is some doubt.

@Vitor, Congrats on being a finalist for 2017 MSSQLTips Author of the Year. Well done :)
1
 
Bill PrewCommented:
Agreed, given the "desired results" in the original question, I will rally around:

SELECT * 
  FROM Table1
 WHERE VersionNo LIKE '%.%.%';

Open in new window


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.