Help with large WHERE expression

[bmacbmac]

Hello. I'm trying to find all rows that have more than 9 commas.

I can get the count of commas with the following query:

select len(field1) - len(replace(field1, ',', '))
from mytable

I actually need the total commas of two fields combined, so I run this:

select ((len(field1) - len(replace(field1, ',', '))) + (len(field2) - len(replace(field2, ',', '))))
from mytable

My question is that I need to know of any instance where the total count of commas is > 9 in any row.

I tried:

select field2
from mytable
where ((len(field1) - len(replace(field1, ',', '))) + (len(field2) - len(replace(field2, ',', ')))) > 9

But I get an incorrect syntax near the keyword 'from'. Can anyone see how I an adjust my 'where' clause or another way to do this?

Thanks!

Brian


I would like to find only those instances

 

[gmmastros]

You were missing a couple of single-quotes.

select field2
from mytable
where ((len(field1) - len(replace(field1, ',', '[!]'[/!]))) + (len(field2) - len(replace(field2, ',', '[!]'[/!])))) > 9

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

DOH! Thanks!