asked on

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

Bill Prew

Why wasn't "1.1" also selected as a valid result, it has two repeating "1"s?

»bp

Bill Prew

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

Sam OZ

ASKER

My apologies. I did not make it clear - I need only repetition of "." captured . Not any other characters

Mark Wills

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

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

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

Vitor Montalvão

Waiting for a better explanation of the problem.

Mark Wills

@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 :)

Bill Prew

Agreed, given the "desired results" in the original question, I will rally around:

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

Open in new window

»bp