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: 33556 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

Nabha

SSCrazy Eights

Points: 8534

September 9, 2009 at 5:04 am

#1050978

Not sure if this will serve the purpose!

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

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

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

Mark Cowne

One Orange Chip

Points: 26865

September 9, 2009 at 5:05 am

#1050979

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

ambannayatnal

SSC Enthusiast

Points: 148

September 9, 2009 at 7:28 am

#1051086

Check this,

declare @STR varchar(100)

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

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

Tejas Shah SQLYoga Old Hand Points: 303 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

SELECT cte.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

Jeff Moden SSC Guru Points: 1000539 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

SELECT cte.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)
Intro to Tally Tables and Functions

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

ChiragNS

One Orange Chip

Points: 26137

September 9, 2009 at 11:06 pm

#1051564

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: 410 More actions · Answer 9

Rob Hershfield

SSC-Addicted

Points: 410

September 22, 2009 at 12:52 pm

#1057062

So darn clever!

Thanks

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions