DECLARE @Yak TABLE (Col1 VARCHAR(20))

INSERT @Yak
SELECT '----11000000'
UNION ALL SELECT '-00022122122'
UNION ALL SELECT '-00020122122'
UNION ALL SELECT '-00021122122'

SELECT
	*
FROM
	@Yak
WHERE
	LEN(Col1) - LEN(REPLACE(Col1, '1', '')) <> 3
	AND CAST(REPLACE(Col1, '-', '') AS INT) < 22122122

declare @s varchar(155)
set @s = '-00022122122'

declare @t table (s varchar(200))
insert @t (s)
SELECT '----11000000'
UNION ALL SELECT '-00022122122'
UNION ALL SELECT '-00020122122'
UNION ALL SELECT '-00021122122'
UNION ALL SELECT '-0001234567899999'
UNION ALL SELECT '-0001234567899'

select t.s
from   @t t
cross apply (
       select case 
              when exists (
                     select d.c
                            ,count(*)
                     from   (
                            select n.number
                                   ,substring(t.s, n.number, 1) c
                            from   master..spt_values n
                            where  n.type = 'p'
                            and    n.number > 0
                            and    n.number <= len(t.s)
                            ) d
                     where  d.c like '%[1-9]%'

                     group by c
                     having count(*) > 3
                            ) then 1
              else 0
              end HasMoreThan3
       ) ca
where  ca.HasMoreThan3 = 0

output:
s                            
-----------------------------
----11000000                
-0001234567899              

quote:

---

Originally posted by akpaga

i am sorry .May be i was not clear.

What I need is to filter the string that
1)if the string has 3 occurrences or more than 3 occurrences of interger 1 in it
ex: 10010010 or 1111000 or 11001100 etc
2)if the string contains atleast one integer greater than 1
ex 000021000, 0330000,0112000 etc

i want to filter out those strings . hope i am clear this time.

i only want records that have 0's and 1's in the string but not more than three 1's

---