Recursive USer-defined funstion problem

18 May 2005 11:05 AM

C_Sheffield

I have created a recursive function count the number of commas in a string,
but it doesn't work and I have no idea why. Code is below. Any help
appreciated

CREATE FUNCTION [dbo].[reps] (@string varchar)
RETURNS int AS
BEGIN
declare @output int

if len(@string) > 1
begin
set @output = dbo.reps(right(@string,len(@string)-1))
if left(@string,1) = ','
begin
set @output = 1 + @output
end
end
else
if len(@string)=1
begin
if @string = ','
    begin
    set @output =1
    end
    else
   set @output =0
end

return @output
end

18 May 2005 11:33 AM

Jens Süßmeyer

Perhaps that might help you, its from Igor who presented these functions
here:

CREATE function OCCURS (@cSearchExpression nvarchar(4000),
@cExpressionSearched nvarchar(4000))
returns smallint
as
    begin
      declare @start_location smallint, @occurs smallint
      select @start_location = dbo.CHARINDEX_BIN(@cSearchExpression,
@cExpressionSearched, 1),   @occurs = 0

     while @start_location > 0
          select @occurs = @occurs + 1, @start_location =
dbo.CHARINDEX_BIN(@cSearchExpression, @cExpressionSearched,
@start_location+1)

     return @occurs
    end
GO

-- Author: Igor Nikiforov, Montreal, EMail: u***@sympatico.ca

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"C_Sheffield" <CSheffi***@discussions.microsoft.com> schrieb im Newsbeitrag
news:A5C56607-111A-4182-8984-465FF1003CA6@microsoft.com...
>I have created a recursive function count the number of commas in a string,
> but it doesn't work and I have no idea why. Code is below. Any help
> appreciated
>
> CREATE FUNCTION [dbo].[reps] (@string varchar)
> RETURNS int AS
> BEGIN
> declare @output int
>
> if len(@string) > 1
> begin
> set @output = dbo.reps(right(@string,len(@string)-1))
> if left(@string,1) = ','
> begin
> set @output = 1 + @output
> end
> end
> else
> if len(@string)=1
> begin
> if @string = ','
>    begin
>    set @output =1
>    end
>    else
>   set @output =0
> end
>
> return @output
> end

18 May 2005 11:42 AM

Mike Epprecht (SQL MVP)

Hi

The SQL Server limitation on nesting is 32, so your implimentation is not
very useful for longer strings.

What error are you getting?

--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"C_Sheffield" wrote:

> I have created a recursive function count the number of commas in a string,
> but it doesn't work and I have no idea why. Code is below. Any help
> appreciated
>
> CREATE FUNCTION [dbo].[reps] (@string varchar)
> RETURNS int AS
> BEGIN
> declare @output int
>
> if len(@string) > 1
> begin
>   set @output = dbo.reps(right(@string,len(@string)-1))
>   if left(@string,1) = ','
>   begin
>   set @output = 1 + @output
>   end
>   end
> else
> if len(@string)=1
> begin
>   if @string = ','
>     begin
>     set @output =1
>     end
>     else
>    set @output =0
> end
>
> return @output
> end

18 May 2005 11:58 AM

C_Sheffield

Hi

If the string starts with a comma , I get a count of 1, otherwise I get 0,
no matter how many commas are in the string I supply.

select dbo.reps('test,test,test') returns 0
select dbo.reps(',test,test,test') returns 1

Thanks
Chris

Show quote

"Mike Epprecht (SQL MVP)" wrote:

> Hi
>
> The SQL Server limitation on nesting is 32, so your implimentation is not
> very useful for longer strings.
>
> What error are you getting?
> --
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
>
>
> "C_Sheffield" wrote:
>
> > I have created a recursive function count the number of commas in a string,
> > but it doesn't work and I have no idea why. Code is below. Any help
> > appreciated
> >
> > CREATE FUNCTION [dbo].[reps] (@string varchar)
> > RETURNS int AS
> > BEGIN
> > declare @output int
> >
> > if len(@string) > 1
> > begin
> >   set @output = dbo.reps(right(@string,len(@string)-1))
> >   if left(@string,1) = ','
> >   begin
> >   set @output = 1 + @output
> >   end
> >   end
> > else
> > if len(@string)=1
> > begin
> >   if @string = ','
> >     begin
> >     set @output =1
> >     end
> >     else
> >    set @output =0
> > end
> >
> > return @output
> > end

18 May 2005 12:33 PM

Hugo Kornelis

On Wed, 18 May 2005 04:05:17 -0700, C_Sheffield wrote:

>I have created a recursive function count the number of commas in a string,
>but it doesn't work and I have no idea why. Code is below. Any help
>appreciated
(snip)

Hi C,

You declare the @string argument as varchar, without length. The default
length is 1. All remainging characters will be lost.

BTW, you really don't need recursion for this:

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

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

18 May 2005 1:50 PM

C_Sheffield

Your way is a lot, lot better, but thanks also for solving why it didn't
work, it was driving me mad.

Thanks again
Chris

Show quote

"Hugo Kornelis" wrote:

> On Wed, 18 May 2005 04:05:17 -0700, C_Sheffield wrote:
>
> >I have created a recursive function count the number of commas in a string,
> >but it doesn't work and I have no idea why. Code is below. Any help
> >appreciated
> (snip)
>
> Hi C,
>
> You declare the @string argument as varchar, without length. The default
> length is 1. All remainging characters will be lost.
>
> BTW, you really don't need recursion for this:
>
> SELECT LEN(@string) - LEN(REPLACE(@string, ',' ,'')
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

18 May 2005 2:07 PM

Raymond D'Anjou

This will not work in all cases:

declare @string varchar(100)
set @string = ', ,' (notice the space between the 2 commas)
SELECT LEN(@string) - LEN(REPLACE(@string, ',' ,''))

....returns 3

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:9ddm81dmplirtnmdlh72dss9d6rot49uk2@4ax.com...
> On Wed, 18 May 2005 04:05:17 -0700, C_Sheffield wrote:
>
>>I have created a recursive function count the number of commas in a
>>string,
>>but it doesn't work and I have no idea why. Code is below. Any help
>>appreciated
> (snip)
>
> Hi C,
>
> You declare the @string argument as varchar, without length. The default
> length is 1. All remainging characters will be lost.
>
> BTW, you really don't need recursion for this:
>
> SELECT LEN(@string) - LEN(REPLACE(@string, ',' ,'')
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

18 May 2005 9:11 PM

Hugo Kornelis

On Wed, 18 May 2005 10:07:48 -0400, Raymond D'Anjou wrote:

>This will not work in all cases:
>
>declare @string varchar(100)
>set @string = ', ,' (notice the space between the 2 commas)
>SELECT LEN(@string) - LEN(REPLACE(@string, ',' ,''))
>
>...returns 3

Hi Raymond,

Good catch! This is caused by removal of the trailing space. It gets
worse with more trailing spaces:

declare @string varchar(100)
set @string = 'abc ,' -- 5 spaces between abc and ,
SELECT LEN(@string) - LEN(REPLACE(@string, ',' ,'')) -- returns 6

And here's the corrected version:

declare @string varchar(100)
set @string = 'abc ,' -- 5 spaces between abc and ,
SELECT LEN(@string + 'X') - LEN(REPLACE(@string + 'X', ',' ,'')) -- 1

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

18 May 2005 11:21 PM

--CELKO--

What if we put markers on either end?

LEN('{' + @string + '}') - LEN(REPLACE('{' + @string + '}', ',' ,''))
- 2

19 May 2005 9:10 PM

Hugo Kornelis

On 18 May 2005 16:21:58 -0700, --CELKO-- wrote:

>What if we put markers on either end?
>
>LEN('{' + @string + '}') - LEN(REPLACE('{' + @string + '}', ',' ,''))
>- 2

Hi Joe,

You don't need the -2, since you add the delimiters in both LEN(...)
expressions.

Further, there's no need for a delimiter at the start. SQL Server will
remove trailing spaces, not leading spaces (as per the ANNSI standard,
IIRC).

After removing the error and removing the unnecessary first delimiter,
this leaves your solution as:

LEN(@string + '}') - LEN(REPLACE(@string + '}', ',' ,''))

And apart from the choice of delimiter ('}' vs 'X'), this is exactly the
same as what I already posted in my corrected version.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

18 May 2005 1:16 PM

CBretana

Why recursivem this problem is easily solvced just by examining each
character in the string...

CREATE FUNCTION dbo.reps (@string varchar)
RETURNS int AS
BEGIN
declare @Count int Set @Count = 0

Declare @C Char(1)
While Len(@string) > 1
     Begin
        Select @C = Left(@string,1),
          @string = Substring(@string, 2,Len(@string) -1)
        If @C = ',' Set @Count = @Count + 1
     End
Return @Count
End

--
Charles Bretana Jr.
Arete Industries Inc.

Show quote

"C_Sheffield" wrote:

> I have created a recursive function count the number of commas in a string,
> but it doesn't work and I have no idea why. Code is below. Any help
> appreciated
>
> CREATE FUNCTION [dbo].[reps] (@string varchar)
> RETURNS int AS
> BEGIN
> declare @output int
>
> if len(@string) > 1
> begin
>   set @output = dbo.reps(right(@string,len(@string)-1))
>   if left(@string,1) = ','
>   begin
>   set @output = 1 + @output
>   end
>   end
> else
> if len(@string)=1
> begin
>   if @string = ','
>     begin
>     set @output =1
>     end
>     else
>    set @output =0
> end
>
> return @output
> end

18 May 2005 4:29 PM

C_Sheffield

Thanks, I just like using recursive scripts in programming, maybe they are
not that suited to SQL though

Show quote

"CBretana" wrote:

> Why recursivem this problem is easily solvced just by examining each
> character in the string...
>
> CREATE FUNCTION dbo.reps (@string varchar)
> RETURNS int AS
> BEGIN
> declare @Count int Set @Count = 0
>
>   Declare @C Char(1)
>   While Len(@string) > 1
>      Begin
>         Select @C = Left(@string,1),
>           @string = Substring(@string, 2,Len(@string) -1)
>         If @C = ',' Set @Count = @Count + 1
>      End
>   Return @Count
> End
>
>
>
> --
> Charles Bretana Jr.
> Arete Industries Inc.
>
>
> "C_Sheffield" wrote:
>
> > I have created a recursive function count the number of commas in a string,
> > but it doesn't work and I have no idea why. Code is below. Any help
> > appreciated
> >
> > CREATE FUNCTION [dbo].[reps] (@string varchar)
> > RETURNS int AS
> > BEGIN
> > declare @output int
> >
> > if len(@string) > 1
> > begin
> >   set @output = dbo.reps(right(@string,len(@string)-1))
> >   if left(@string,1) = ','
> >   begin
> >   set @output = 1 + @output
> >   end
> >   end
> > else
> > if len(@string)=1
> > begin
> >   if @string = ','
> >     begin
> >     set @output =1
> >     end
> >     else
> >    set @output =0
> > end
> >
> > return @output
> > end