Count number of digits in text

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

General SQL Server Forums

New to SQL Server Programming

Count number of digits in text

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-07-20 : 13:31:58

Hi guys,

I'm trying to write a producure to count the number of digits in a text.

i.e :
text
---------
12 fff 3 a.z
12345 ggg6gg a
123

Result:
--------
3
6
3

It seems to be real easy via RegEx in C# , but i cant figure it out in SQL.

Thank YOU!

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 16:22:50

Here's a function that should do it:

CREATE FUNCTION fn_GetCountOfNumbers
(@InputText varchar(8000))
RETURNS int
AS
BEGIN
declare @len as int
declare @ctr as int
declare @chr as char(1)

set @len=len(@InputText)
set @ctr = 0

while (@len>0)
begin
set @chr = substring(@InputText,@len,1)
if isnumeric(@chr) = 1
set @ctr = @ctr + 1
set @len = @len - 1
end
return @ctr
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-20 : 16:39:13

[code]-- Prepare test data
declare @t table (z varchar(100))

insert @t
select '12 fff 3 a.z' union all
select '12345 ggg6gg a' union all
select '123'

-- Do the work
select z,
len(z) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(z,
'9', ''), '8', ''), '7', ''), '6', ''), '5', ''), '4', ''), '3', ''), '2', ''), '1', ''), '0', ''))
from @t[/code]

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 06:07:23

Peso's function will be faster, I think - if that it important to you.

BUT use "datalength" rather than "len", otherwise you will have issues with data like '1 2' (which returns 3 with len, but 2 with datalength).

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-21 : 08:52:19

Good point!

-- Do the work
select	z,
	Datalength(z) - Datalength(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(z,
	'9', ''), '8', ''), '7', ''), '6', ''), '5', ''), '4', ''), '3', ''), '2', ''), '1', ''), '0', ''))
from	@t

Peter Larsson
Helsingborg, Sweden

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-07-23 : 18:01:13

Thank you Guys!!

yes , speed is very important because i have alot of records .
I'm using Peso code and it works great.
Is using Datalength faster/slower than usign Len? or it doesn't make any difference.

Ty again.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-24 : 04:37:27

"datalength" counts trailing spaces.
"len" does NOT count trailing spaces.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-07-24 : 08:22:38

Yep, I understood that , as i see it the end results of both is the same.
My question is regarding performance. i have alot of records and this query seems to be time consuming.
Any perfomance differences?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 08:37:04

[code]declare @s varchar(3)

select @s = '1 2'

select @s 'Original String',
len(@s) - len(replace(replace(@s, '1', ''), '2', '')) 'digits using Len',
datalength(@s) - datalength(replace(replace(@s, '1', ''), '2', '')) 'digits using DataLength'[/code]

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-24 : 08:47:59

Not much point in worrying about the speed...if the function doesn't work as required. my point was to establish this as a minimum requirement. re speed....how many is 'a lot of reords'? what is your execution plan?

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-07-24 : 08:53:24

60k records.
I'm asking to try to make the query more efficient because i started getting timeout errors:

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

but as you said , first goal is for function should work as required :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-25 : 03:59:53

Are you running this through Enterprise Manager or something? How long does it take before it times out...? 60k records is not a lot so there must be something going on...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources