Number of occurances - SQL Server Forums

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)

Number of occurances

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2009-01-16 : 14:56:24

I have created a simple select and am returning two large fields. Is there a way to count the number of occurances, of periods, there are in a singe field?

Robert R. Barnes

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-16 : 15:00:53

Could you please post some sample data and expected result? It's not clear (at least to me) what you mean by fields, whether it is rows or columns.

Lopaka
Starting Member

48 Posts

Posted - 2009-01-16 : 15:07:40

declare @tbl Table(
@fld1 varchar(25))

INSERT INTO @tbl(@fld1) VALUES('a.b.c.d.e')
INSERT INTO @tbl(@fld1) VALUES('b.c.d.e')
INSERT INTO @tbl(@fld1) VALUES('c.d.e')

The select would return

fld1, occurance
a.b.c.d.e, 4
b.c.d.e, 3
c.d.e, 2

Thank you...:)

Robert R. Barnes

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-16 : 15:42:22

declare @tbl Table(
fld1 varchar(25))

INSERT INTO @tbl(fld1) VALUES('a.b.c.d.e')
INSERT INTO @tbl(fld1) VALUES('b.c.d.e')
INSERT INTO @tbl(fld1) VALUES('c.d.e')

SELECT fld1, (LEN(fld1) - LEN(REPLACE(fld1, '.', ''))) [Count] FROM @tbl

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-01-16 : 17:11:34

simple math, i like.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-16 : 17:50:41

It is better to use the DATALENGTH function, instead of the LEN function.

This code shows why:

select
	x,
	DL_X	= datalength(x),
	L_X	= len(x),
	DL_XR	= datalength(replace(x,'.','')),
	L_XR	= len(replace(x,'.',''))
from
	( select x='   .   ' ) a

Results:
x       DL_X        L_X         DL_XR       L_XR        
------- ----------- ----------- ----------- ----------- 
   .    7           4           6           0

(1 row(s) affected)

CODO ERGO SUM

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-16 : 19:57:22

Using DATALENGTH instead of LEN will give incorrect results using Skorch's method if the data type is NVARCHAR. Even with trailing spaces, Skorch's method will work correctly because SQL will ignore the trailing spaces in both instances where LEN function is used in the SELECT statement, except in cases where the string contains only spaces and the character you are trying to count.

an alternate method might be to slightly modify Skorch's query as:

SELECT fld1, (LEN(fld1 + 'X' ) - LEN(REPLACE(fld1 + 'X', '.', ''))) [Count] FROM @tbl

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 07:07:59

If you know the input value is NVARCHAR, still use DATALENGTH(x) - DATALENGTH(REPLACE(x, '.', ''))
and divide result by two. Still simple math.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources