Count of special characters in a column

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)

Count of special characters in a column

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-03 : 11:21:22

Hi Team,

I am trying to find the count of special characters in a column. One more constraint is that "[" and "]" should not be counted for the Count.

In the following example, the expected result is 3
CREATE TABLE #Template (TemplateID INT, Body VARCHAR(100))
INSERT INTO #Template (TemplateID,Body) VALUES (1,'[[Date%!@]]')

Could you please tell me how to do it?

Thanks
Lijo

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 12:34:11

This is like Madhi's other answer to you. You didn't define what constitutes a special character

select len(body) - count(*) -4 as SpecChar
from #template r
cross apply
(select number as n from master..spt_values where [type]= 'p') a
where
substring(replace(replace(body,'[[',''),']]',''),n,1) like '%[a-z]%'-- get the chareacters that aren't special
group by len(body)

Jim

Everyday I learn something that somebody else already knew

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-03 : 12:39:53

Anything other than the following is a special character for this scenario
1) Alphabtes
2) Digits
3) Space
4) [
5) ]

Your above solution does not meet the requirement. I need 3 as the result in the following scenario also.

CREATE TABLE #Template (TemplateID INT, Body VARCHAR(100))
INSERT INTO #Template (TemplateID,Body) VALUES (1,'[[Date%!@]][]')

select len(body) - count(*) -4 as SpecChar
from #template r
cross apply
(select number as n from master..spt_values where [type]= 'p') a
where
substring(replace(replace(body,'[[',''),']]',''),n,1) like '%[a-z]%'-- get the chareacters that aren't special
group by len(body)

DROP TABLE #Template

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources