-1

HI i have a table 'TableCustomers' and within this table are many fields titled 'Name1' 'Name2''Name3'.... 'Name40' some of these fields just have the letter 'x' i want to know how many 'x' are there in all 40 fields

Rashed Farah
  • 19
  • 1
  • 1
  • 2
  • What have you tried so far? Please include a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Please also read [How do I ask a Good Question](http://stackoverflow.com/help/how-to-ask). Make sure that your questions are specific and not overly broad. – Igor Jun 23 '16 at 15:09
  • Possible duplicate? http://stackoverflow.com/questions/591853/search-for-a-string-in-all-tables-rows-and-columns-of-a-db – user3378165 Jun 23 '16 at 15:10
  • Possible duplicate of [How to count the number of times a character appears in a SQL column?](http://stackoverflow.com/questions/1144172/how-to-count-the-number-of-times-a-character-appears-in-a-sql-column) – JNevill Jun 23 '16 at 15:10
  • 1
    Those questions relate to counting the occurrences within a single column, however i think op wants to count number of columns it occurs within, for a single row? – Milney Jun 23 '16 at 15:16

2 Answers2

0

One possible solution is something like;

SELECT 
CASE WHEN [Name] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name1] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name2] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name3] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name4] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name5] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name6] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name7] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name8] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name9] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name10] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name11] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name12] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name13] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name14] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name15] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name16] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name17] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name18] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name19] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name20] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name21] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name22] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name23] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name24] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name25] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name26] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name27] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name28] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name29] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name30] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name31] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name32] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name33] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name34] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name35] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name36] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name37] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name38] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name39] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name40] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name41] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name42] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name43] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name44] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name45] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name46] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name47] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name48] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name49] = 'x' THEN 1 ELSE 0 END +
CASE WHEN [Name50] = 'x' THEN 1 ELSE 0 END
FROM [TableCustomers]

I generated this using an application called Nimble Text (http://nimbletext.com/) though you could use dynamic SQL along with the sys.columns view to generate this statement within SQL server, if the columns of the table change. Let me know if you would like an example of this...

Milney
  • 6,253
  • 2
  • 19
  • 33
0

You may try something like this:

    select len(name00 + name01 + .. + name40) - len(replace(name00 + name01 + .. + name40, 'x', '')) 
from yourTable;
JuanN
  • 698
  • 1
  • 13
  • 21