An easy question?

Just had someone else ask me a question I can't seem to find the answer to anywhere.

Is there a way to count the number of instances of a particular character within a string?

He has an array that is sourced from VB that inserts values into a table with a pipe delimiter and he wants to count how many times the delimiter appears in each row.

I've looked in BOL, and can't find anything that would allow me to do this. Anyone have any suggestions?

Thanks,
Dirk

PS - this is SQL7Can you provide more information about the vb code inserting ? What are you trying to accomplish by counting the delimiter ?|||I'm not really sure why he wants to count them. Our web guy has this application and he asked me if I could help him figure this out. Everyone else he's asked told him it couldn't be done, but I disagree. I just don't know how to do it easily. I know how I could probably accomplish it in an unconventional and cumbersome manner using patindex/charindex and some math, but I was hoping there was a simple way.

The field contains answers to questions in a survey, and each answer is delimited by a pipe. Not sure why the determination to store them as an array was made either.

Dirk|||So the field stores many strings delimited by a special character. What is the data type for this field ?|||Have you thought about modifying the split function that I posted to do this ?|||Yes. Currently the type is set to nvarchar(50). My guess is that this may need to be expanded somewhere down the road, but the type will not change.

I did consider the split function, I'm just not sure how to modify it in our current environment. I'm actually working on that now.

Dirk|||The problem is that there are not that many string functions in sql server - however, there are in vb - Can you do this in vb rather than sql server ?|||Yes, he can do it in vb but for some reason he wants to create this as a function or a stored procedure. Those web guys are a little strange sometimes. Or perhaps he wants to call it in other (future) databases or something and doesn't want to rewrite it every time...

If it can't be done fairly easily, that's OK though. I can always tell him it needs to be a vb process.
Dirk|||Something like this would give you a count of a specific character in a varchar. The value is stored in @.counter. The string being sought in this case is the pipe (|). The varchar being searched is @.compareString.
Unfortunately, this is the easiest way that I found (using charindex).

declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)

set @.compareString='I|was|here|today|'
set @.counter=0
select @.foundAt=charindex('|',@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('|',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter

That might work...|||This does look like it would work, but I'm having trouble setting the variable @.comparestring to be the column in the table. I keep getting "invalid column name"??

Thanks for this sample code though - it looks very promising!
Dirk|||You could try:

select @.compareString = (select [Column Name] from [Table Name] where [Condition])

or

select @.compareString = [Column Name] from [Table Name] where [Condition]

The only catch is that your [Condition] should return exactly one value for [Column Name], otherwise it will bomb out. I haven't figured out how to put that in a loop and check multiple records yet. If you figure it out, please post here or email me.

Thanks,

Roman|||I would wrap all of this into a function that accecpts the "@.compareString" and the delimiter, the return would be the number of delimiters.

try this...
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'CountDelimiter')
DROP FUNCTION CountDelimiter
GO

CREATE FUNCTION CountDelimiter(
@.compareString varchar(50),
@.delimiter varchar(1))
RETURNS int
AS
BEGIN
declare @.foundAt int, @.counter int
set @.counter=0
set @.foundAt=charindex(@.delimiter,@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1
while @.foundAt>0 begin
select @.foundAt=charindex(@.delimiter,@.compareString,@.foun dAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
return @.counter
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.CountDelimiter('I|was|here|today|','|')
GO

now you can use this in a select statment or sp.|||Paul Young's way is better, but here's a more asinine way anyway :}

declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)

DECLARE abc CURSOR FOR
SELECT [Column Name] FROM [Table Name] where [Condition]

OPEN abc

FETCH NEXT FROM abc into @.compareString
WHILE (@.@.FETCH_STATUS = 0)
begin
set @.counter=0
select @.foundAt=charindex('L',@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('L',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter
FETCH NEXT FROM abc into @.compareString
end

CLOSE abc
DEALLOCATE abc
GO|||a thousand lashes with a wet noodle for suggesting the use of a cursor!!!|||:)

Whatever it takes to make it work.|||of course, I was just kidding!|||Here is what I did to get multiple rows - I just turned it into a cursor inside a procedure. Maybe not the most effective method, but I'm just happy it actually works. The only problem I have with it now is that it only returns me the delimiter value, and I also need the associated QID. I thought I accounted for it, but apparently I didn't.

Too late in the day for my brain. If anyone can help that would be great. I'll look at the above solution a little later. Thanks to all for the help!

Dirk

CREATE procedure [dbo].[countdelim]
as
declare @.qid int
declare @.fullstring varchar (100)

declare count_cursor cursor

for select QuestionID, fulltexttoparse from Table1

open count_cursor
fetch next from count_cursor into @.qid, @.fullstring
while (@.@.fetch_status = 0)
begin
--set @.qid=(select QuestionID from Table1)
declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)
set @.compareString=@.fullstring
set @.counter=0
select
--@.qid,
@.foundAt=charindex('|',@.compareString)
--from Table1
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('|',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter

fetch next from count_cursor into @.qid, @.fullstring
end

close count_cursor
deallocate count_cursor|||At least lashes with a wet noodle aren't fatal. Go ahead and whip me now.

Dirk|||Just add a print @.qid where you print @.counter|||Hang on big boy I don't do that for just anyone!! ;)

If a solution is not posted by tomorrow morning I will take a look at it.|||Originally posted by rnealejr
Just add a print @.qid where you print @.counter

Actually I tried this already and it didn't give me a two column result. I wanted @.QID in column1 and @.COUNTER in column 2. It jumbled everything together in a single column.

I tried PRINT @.QID, @.COUNTER which gave me an error so I added a second print line that gave the above results.

Syntax gets me every time.
Dirk|||Paul - He can't use the function since he is using ss7.

So in order for you to loop through all the records you will have to use a cursor - functions are a huge benefit in ss2k.

Print is strictly used to display back to the user - not displaying data like you see when you do a sql select statement. You can create a temporary table to store the values then do a select * from #table. If you had ss2k, you could use a table data type.|||Try the following - I put '***' before the statements I added:

CREATE procedure [dbo].[countdelim]
as
declare @.qid int
declare @.fullstring varchar (100)

***create table #hold(someid int, counter int)
-- create the temporary table - modify based on what you need to store

declare count_cursor cursor

for select QuestionID, fulltexttoparse from Table1

open count_cursor
fetch next from count_cursor into @.qid, @.fullstring
while (@.@.fetch_status = 0)
begin
--set @.qid=(select QuestionID from Table1)
declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)
set @.compareString=@.fullstring
set @.counter=0
select
--@.qid,
@.foundAt=charindex('|',@.compareString)
--from Table1
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('|',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter
***insert into #hold values (@.qid, @.counter)

fetch next from count_cursor into @.qid, @.fullstring
end
***select * from #hold
***drop table #hold

close count_cursor
deallocate count_cursor|||1. I DONT LIKE CURSORS AT ALL!

--creating accel counter table (faster then inline)
if object_id('dbo.ufn_SmallintSequence1_IC') is not null drop function dbo.ufn_SmallintSequence1_IC
GO
create function dbo.ufn_SmallintSequence1_IC(@.EndNumber smallint)
returns @.table table (Number smallint primary key clustered) as begin
insert @.table(Number)
select Number=convert(smallint,( X0.X + 4*X1.X + 16*X2.X + 64*X3.X + 256*X4.X + 1024*X5.X + 4096*X6.X + 16384*X7.X+1 ))
from ( select 0 as X union all select 1 union all select 2 union all select 3 ) X0 --4
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X1 --16
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X2 --64
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X3 --256
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X4 --1024
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X5 --4096
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X6 --16384
cross join ( select 0 as X union all select 1 ) X7 --32768
where convert(smallint,( X0.X + 4*X1.X + 16*X2.X + 64*X3.X + 256*X4.X + 1024*X5.X + 4096*X6.X + 16384*X7.X ))<@.EndNumber
RETURN
end
GO
--SINGLE QUARY TEMPLATE( cross join is fastest - I wonder)
select yt.YourIdCol,sum(case when charindex('|',yt.YourCol,c.Number)>0 then 1 else 0 end)
from YourTable yt
cross join dbo.ufn_SmallintSequence1(8000) c
group by yt.YourIdCol

Maybe this helped...|||ispaleny - Yes, cursors are bad ... but as already mentioned, this is ss7 - so functions and table data types are of no use.|||I was speaking with God this night. She said: Dont be in range about cursors, use replace!

select len(YourCol)-len(replace(YourCol,'|',''))
from YourTable yt|||You have got to love the simplicity - beautiful.|||RE:

I was speaking with God this night. She said: Dont be in range about cursors, use replace!

select len(YourCol)-len(replace(YourCol,'|',''))
from YourTable yt

Resplendent :)|||Originally posted by tnjazzgrass
I'm not really sure why he wants to count them. Our web guy has this application and he asked me if I could help him figure this out. Everyone else he's asked told him it couldn't be done, but I disagree. I just don't know how to do it easily. I know how I could probably accomplish it in an unconventional and cumbersome manner using patindex/charindex and some math, but I was hoping there was a simple way.

The field contains answers to questions in a survey, and each answer is delimited by a pipe. Not sure why the determination to store them as an array was made either.

Dirk|||The only restriction is that the original post indicated that the platform was SQL 7. SQL 7 does not support UDFs.

Can you modify the table to add a field and store the number of values in the field?

When the values are inserted, the developer can use the Split function and UBound to determine the number of values.

Regards,

Hugh Scott

Originally posted by Paul Young
I would wrap all of this into a function that accecpts the "@.compareString" and the delimiter, the return would be the number of delimiters.

try this...
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'CountDelimiter')
DROP FUNCTION CountDelimiter
GO

CREATE FUNCTION CountDelimiter(
@.compareString varchar(50),
@.delimiter varchar(1))
RETURNS int
AS
BEGIN
declare @.foundAt int, @.counter int
set @.counter=0
set @.foundAt=charindex(@.delimiter,@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1
while @.foundAt>0 begin
select @.foundAt=charindex(@.delimiter,@.compareString,@.foun dAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
return @.counter
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.CountDelimiter('I|was|here|today|','|')
GO

now you can use this in a select statment or sp.|||true, but I think ispaleny supplied the ultimate answer.|||Originally posted by ispaleny
I was speaking with God this night. She said: Dont be in range about cursors, use replace!

select len(YourCol)-len(replace(YourCol,'|',''))
from YourTable yt

Unbelievable. It's amazing how the simplest solutions can sit right under your nose and you still take the long way around. I just showed this to our web programmer and he fell out of his chair.

Thanks everyone!
Dirk