How can I count any character in a column?

Question

Post reply

How can I count any character in a column?

neo_pom

SSC Veteran

Points: 241
More actions
September 9, 2009 at 4:32 am

#156191

If a column keeps email address look like these
p@hotmail.com;a@hotmail.com
I'd like count '@' in the column
How can I write TSQL just only one statement(without loop) to do that ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Try this

declare @String varchar(255)

Select @String = 'dsasda@fff@ffff@';

with Nums(n)AS (

select top 100 percent number from

master..spt_values

where

TYPE='p' and number =1

order by number

),

CharList(n,CharAT)

as

(

Select n,substring(@String,n,1)

from Nums

)

select count(*) from CharList where CharAt = '@'

Clear Sky SQL
My Blog[/url]

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

Not sure if this will serve the purpose!

Select Len('SQLSERVER') - LEN(REPLACE('SQLSERVER','S', ''))

---------------------------------------------------------------------------------

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

SELECT LEN(@String)-LEN(REPLACE(@String,'@',''))

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ambannayatnal SSC Enthusiast Points: 148 More actions · Answer 4

Check this,

declare @STR varchar(100)

set @STR='mail1@hotmail.com; mail2@hotmail.com'

select LEN(@str) - LEN(REPLACE(@str, '@', ''))

Tejas Shah Old Hand Points: 308 More actions · Answer 5

Hi,

You can achieve the same by this query.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(100))

INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;tejas@SQLY@oga.com'

INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;'

;WITH CTE AS(

SELECT ID,

SUBSTRING(data,0, LEN(data)+1) AS Data,

LEN(data) AS Level,

SUBSTRING(data, LEN(data), 1) AS Character

FROM @t

UNION ALL

SELECTcte.ID,

SUBSTRING(t.data,0, LEN(cte.data)) AS Data,

LEN(cte.data) - 1 AS Level,

--'' AS Character

SUBSTRING(cte.data, LEN(cte.data)-1, 1) AS Character

FROM @t t

INNER JOIN cte ON t.ID = cte.ID

AND cte.Level > 0

)

select ID, COUNT(*) [No Of @]

from cte

WHERE Character like '@'

GROUP BY ID

ORDER BY ID

let us know if it helps you.

Tejas Shah

http://www.SQLYoga.com

Tejas Shah

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Tejas Shah (9/9/2009)
Hi,
You can achieve the same by this query.
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(100))
INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;tejas@SQLY@oga.com'
INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;'
;WITH CTE AS(
SELECT ID,
SUBSTRING(data,0, LEN(data)+1) AS Data,
LEN(data) AS Level,
SUBSTRING(data, LEN(data), 1) AS Character
FROM @t
UNION ALL
SELECTcte.ID,
SUBSTRING(t.data,0, LEN(cte.data)) AS Data,
LEN(cte.data) - 1 AS Level,
--'' AS Character
SUBSTRING(cte.data, LEN(cte.data)-1, 1) AS Character
FROM @t t
INNER JOIN cte ON t.ID = cte.ID
AND cte.Level > 0
)
select ID, COUNT(*) [No Of @]
from cte
WHERE Character like '@'
GROUP BY ID
ORDER BY ID
let us know if it helps you.
Tejas Shah
http://www.SQLYoga.com

I'd recommend NOT using a recursive CTE for anything except for (possibly) hierarchies because they are as slow as a While Loop. Take a look at the other solutions which require neither and are much shorter and performant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

didn't think about this

SELECT LEN(@String)-LEN(REPLACE(@String,'@','')) 🙂

"Keep Trying"

arun.sas SSChampion Points: 11831 More actions · Answer 8

neo_pom (9/9/2009)
If a column keeps email address look like these
p@hotmail.com;a@hotmail.com
I'd like count '@' in the column
How can I write TSQL just only one statement(without loop) to do that ?

Hi,

Suppose the mailId stored in the text data type column, then first remove all the empty space,

and then finding the repeating characters.

declare @abc table (mails text)

insert into @abc (mails)

select '1@123.com ; 12@123.com'

union all

select ' 123@123.com ; 1234@123.com '

update @abc

set mails = (replace(cast(mails as char(8000)),' ',''))

select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc

RESULT

2

declare @abc table (mails text)

insert into @abc (mails)

select '1@123.com ; 12@123.com'

union all

select ' 123@123.com ; 1234@123.com '

--update @abc

--set mails = (replace(cast(mails as char(8000)),' ',''))

select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc

RESULT

2

3

Rob Hershfield SSC-Addicted Points: 440 More actions · Answer 9

Rob Hershfield

SSC-Addicted

Points: 440

September 22, 2009 at 12:52 pm

#1057062

So darn clever!

Thanks

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

arun.sas (9/9/2009)
neo_pom (9/9/2009)
If a column keeps email address look like these
p@hotmail.com;a@hotmail.com
I'd like count '@' in the column
How can I write TSQL just only one statement(without loop) to do that ?
Hi,
Suppose the mailId stored in the text data type column, then first remove all the empty space,
and then finding the repeating characters.
declare @abc table (mails text)
insert into @abc (mails)
select '1@123.com ; 12@123.com'
union all
select ' 123@123.com ; 1234@123.com '
update @abc
set mails = (replace(cast(mails as char(8000)),' ',''))
select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc
RESULT
2
2
declare @abc table (mails text)
insert into @abc (mails)
select '1@123.com ; 12@123.com'
union all
select ' 123@123.com ; 1234@123.com '
--update @abc
--set mails = (replace(cast(mails as char(8000)),' ',''))
select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc
RESULT
2
3

Ummm... nope... there's only 2 "@" in both lines.

Simple mod does make it work, though.

declare @abc table (mails text)

insert into @abc (mails)

select '1@123.com ; 12@123.com'

union all

select ' 123@123.com ; 1234@123.com '

--update @abc

--set mails = (replace(cast(mails as char(8000)),' ',''))

select (Datalength(cast(mails as VARchar(8000))) - Datalength(REPLACE(cast(mails as VARchar(8000)), '@', ''))) from @abc

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)