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.
