character count per column

[Cornelius19]

Hi,

I have a column containing words. I would like to list all the characters and count how many times each character occurred in the entire column (not per word/record). There are some non-ASCII characters. Do you have any SQL query or script to do this?

Thanks,

Cornelius

 

[gmmastros]

Maybe something like this...

[COLOR=blue]Select[/color] *,
       Len(word) - Len([COLOR=#FF00FF]Replace[/color](Word, [COLOR=red]'A'[/color], [COLOR=red]''[/color])) [COLOR=blue]As[/color] NumberOfA,
       Len(word) - Len([COLOR=#FF00FF]Replace[/color](Word, [COLOR=red]'B'[/color], [COLOR=red]''[/color])) [COLOR=blue]As[/color] NumberOfB,
       Len(word) - Len([COLOR=#FF00FF]Replace[/color](Word, [COLOR=red]'C'[/color], [COLOR=red]''[/color])) [COLOR=blue]As[/color] NumberOfC
[COLOR=blue]From[/color]   Words

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Cornelius19]

Hi,

Thanks a lot, it resolved half of my problem: for any specific character, I can get the total number of occurrences by creating a count table with the above query and adding a Sum(count.NumberOfA) etc. query. It even works for non-ASCII characters.

However, I still have two issues:

1) I do not have list of characters the word column contains. How to create such a list?
2) Is there a way to make the count case sensitive?

Cornelius

 

[gmmastros]

Case Sensitive...

[COLOR=blue]Select[/color] *,
       Len(word) - Len([COLOR=#FF00FF]Replace[/color](Word, [COLOR=red]'A'[/color] [!]Collate SQL_Latin1_General_CP1_CS_AS[/!], '')) As NumberOfA,
       Len(word) - Len([COLOR=#FF00FF]Replace[/color](Word, [COLOR=red]'a'[/color] [!]Collate SQL_Latin1_General_CP1_CS_AS[/!], '')) As NumberOfa,
       Len(word) - Len([COLOR=#FF00FF]Replace[/color](Word, [COLOR=red]'B'[/color], [COLOR=red]''[/color])) [COLOR=blue]As[/color] NumberOfB,
       Len(word) - Len([COLOR=#FF00FF]Replace[/color](Word, [COLOR=red]'C'[/color], [COLOR=red]''[/color])) [COLOR=blue]As[/color] NumberOfC
[COLOR=blue]From[/color]   Words

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Cornelius19]

Thanks, the case sensitive search works now fine.

Cornelius