ansaurus

Question

Answer 1

+5 A:

if ms sql

SELECT LEN(REPLACE(myColumn, 'N', '')) FROM ...

David Hedlund 2009-12-07 14:57:48

Very clever solution!

cindi 2009-12-07 14:58:42

thanks =) please tick the answer as accepted if you're happy with it

David Hedlund 2009-12-07 14:59:20

Just be aware that if there are more than "N" or "Y" in the string then this could be inaccurate. See nickf's solution for a more robust method.

Tom H. 2009-12-07 15:28:10

Answer 2

+1 A:

Maybe something like this...

SELECT
    LEN(REPLACE(ColumnName, 'N', '')) as NumberOfYs
FROM
    SomeTable

Jason Punyon 2009-12-07 14:58:19

Answer 3

+8 A:

This snippet works in the specific situation where you have a boolean: it answers "how many non-Ns are there?".

SELECT LENGTH(REPLACE(`col`, 'N', ''))

If, in a different situation, you were actually trying to count the occurrences of a certain character in any given string, use this:

SELECT LENGTH(col) - LENGTH(REPLACE(col, 'Y', ''))

nickf 2009-12-07 14:58:21

How to count instances of character in SQL Column