Clever way to count characters in a string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Clever way to count characters in a string?

Author

Topic

scoots987
Starting Member

6 Posts

Posted - 2010-05-10 : 16:23:36

I was just asked to compile some stats. One of the requests is dealing with counting characters in multiple fields.

Are there clever ways to count the number of the character "A" are in a field across a record for a few selected fields?

For the sake of simplicity, if we had 8 fields in a table that are varchar(15) and we want to know how many "A's" there are in the entire table or in these 8 fields?

Much appreciate any thoughts to how this would be done in SQL?

TIA.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-10 : 16:44:39

You mean something like this?


declare @i  table (col1 varchar(20), col2 varchar(20))
insert @i select 'aaaaaaabbaaaaaaba', 'aaaaaaaaarteaaataata' union
select 'awerwqaa', 'asdfqwerza'

;with cte as 
(select 1 as i 
union all
select i+1 from cte where i<100),
numbs  as
(select row_number() over(order by (select null)) as i from cte c0 cross join cte)

select col1, col2, d1.i+d2.i as count
from @i t
cross apply (select count(*)
              from numbs
				where i <= len(t.col1)
				and 'a'=substring(t.col1, i, 1))d1(i)
cross apply (select count(*)
              from numbs
				where i <= len(t.col2)
				and 'a'=substring(t.col2, i, 1))d2(i)

/*
col1                 col2                 count
-------------------- -------------------- -----------
aaaaaaabbaaaaaaba    aaaaaaaaarteaaataata 29
awerwqaa             asdfqwerza           5
*/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-10 : 16:50:23

Another way:


SELECT column1, column2,...  LEN(string) - LEN(REPLACE(string, 'A', '')) AS count
  FROM (SELECT column1, column2,... , column1 + column2 + ... AS string
          FROM YourTable) AS T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-11 : 03:23:51

http://beyondrelational.com/blogs/madhivanan/archive/2007/12/21/count-number-of-words-in-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

scoots987
Starting Member

6 Posts

Posted - 2010-05-12 : 11:50:52

Thanks to all for the reply's. I especially appreciated the simplicity of this one.

But the other one introduced some new commands I want to explore.

So again thanks!!!

quote:

Originally posted by malpashaa

Another way:


SELECT column1, column2,...  LEN(string) - LEN(REPLACE(string, 'A', '')) AS count
  FROM (SELECT column1, column2,... , column1 + column2 + ... AS string
          FROM YourTable) AS T

mohbatrai
Starting Member

3 Posts

Posted - 2012-07-24 : 04:03:44

EName
sana
saleem
aijaz
mohbat
aasia
asad ali
asif
aftab
sagar
hanif

ename is column name, I want to select those name who have single 'a' .
output:
saleem
mohbat
asif
hanif

kindly send me query.
i used insr() function but this is not show proper output.

I want to join forum

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 04:42:32

select Ename from table
where len(Ename)-len(replace(Ename,'a',''))=1

Madhivanan

Failing to plan is Planning to fail

mohbatrai
Starting Member

3 Posts

Posted - 2012-07-25 : 02:15:20

Thanks alot

mohbatrai
Starting Member

3 Posts

Posted - 2012-07-26 : 02:48:35

Dear you assign me task in sqlplus. i mean ask difficult queries.

I want to join forum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 03:25:03

quote:
Originally posted by mohbatrai

ename is column name, I want to select those name who have single 'a'.

SELECT * FROM dbo.Table1 WHERE Col1 LIKE '%a%' AND Col1 NOT LIKE '%a%a%'

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources