CHECK DOUBLE RECORD

hi,
I've problem to check row which having duplicate value
for fieldA and fieldC.
Eg.
rec 1
fieldA fieldB fieldC
50000A 123456 A
rec 2
50000A 654321 A
Thanks.
see following example:
create table test(c1 varchar(10), c2 varchar(10), c3 char(1))
insert into test values('50000A','123456','A')
insert into test values('50000A','654321','A')
insert into test values('50000X','654321','A')
query:
select a.*
from test a join (Select c1,c3 from test group by c1,c3 having count(*) >
1) b
on a.c1 = b.c1 and a.c3=b.c3
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

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.