Check 3 occurrences of double characters.

Hi all,

I need to write some sort of statement to identify which numbers from a list fall into the following pattern:

% TwoIdenticalNumbers % TwoIdenticalNumbers % TwoIdenticalNumbers %

for example:

08812355677

I thought I would be able to use a LIKE statement but I'm not sure how to write it so that double characters are used rather than single. For example if I wanted to check three numbers appear within the string I could do the following:

SELECT *

FROM Table

WHERE Number LIKE '%[0-9]%[0-9]%[0-9]%'

I think the easiest way to check the doubles would be to represent them as a string, so I want to replace each of the [0-9] above with something like the following:

['00' OR '11' OR '22' OR '33' OR '44' OR '55' OR '66' OR '66' OR '77' OR '88' OR '99']

How could I write this using proper SQL code?

Any help would be much appreciated.

Thanks very much,

Will

Thre is no predefined expressions available,

Following approach is one of the way to achive this,

Code Snippet

Create Table #data (

[Numbers] Varchar(100)

);

Insert Into #data Values('1242432');

Insert Into #data Values('242423423');

Insert Into #data Values('2332232');

Insert Into #data Values('828289');

Insert Into #data Values('99887766');

Insert Into #data Values('92829299');

select

numbers

from #data

cross join

(

select '00' n

union all

select '11'

union all

select '22'

union all

select '33'

union all

select '44'

union all

select '55'

union all

select '66'

union all

select '77'

union all

select '88'

union all

select '99' ) as d

group by numbers having sum(case when patindex('%'+ n + '%',numbers) <> 0 Then 1 Else 0 End) >= 3

|||

:-) Looks oofy, but works

SELECT * FROM sysobjects

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'

|||

The idea is good, but the following query might fit the asked requirement,(3 occurrence of …)

Code Snippet

SELECT Id FROM sysobjects

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%X%X%'

|||

Very close. At least three instances are needed.

'11' -- does not qualify

'1122' -- does not qualify

'112233' -- bingo

...

SELECT

*

FROM

(select '11' as ID) as t

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'

AMB

|||

Talking about ugly. Try:

-- thanks to Manni for the sample data

select

numbers

from

#Data

where

(len(numbers) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(numbers, '00', ''), '11', ''), '22', ''), '33', ''), '44', ''), '55', ''), '66', ''), '77', ''), '88', ''), '99', ''))) / 2 >= 3

AMB

|||Thanks for all the help, this has saved me a lot of time trying to figure this one out.