Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Jun 2002
    Posts
    36

    Unanswered: 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 SQL7

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Can you provide more information about the vb code inserting ? What are you trying to accomplish by counting the delimiter ?

  3. #3
    Join Date
    Jun 2002
    Posts
    36
    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

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    So the field stores many strings delimited by a special character. What is the data type for this field ?

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Have you thought about modifying the split function that I posted to do this ?

  6. #6
    Join Date
    Jun 2002
    Posts
    36
    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

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  8. #8
    Join Date
    Jun 2002
    Posts
    36
    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

  9. #9
    Join Date
    Jan 2003
    Location
    Chicago, IL, USA
    Posts
    8
    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...
    Last edited by roman22; 01-08-03 at 15:14.

  10. #10
    Join Date
    Jun 2002
    Posts
    36
    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

  11. #11
    Join Date
    Jan 2003
    Location
    Chicago, IL, USA
    Posts
    8
    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

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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...
    Code:
    -- =============================================
    -- 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,@foundAt+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
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Jan 2003
    Location
    Chicago, IL, USA
    Posts
    8
    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
    Last edited by roman22; 01-08-03 at 16:20.

  14. #14
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    a thousand lashes with a wet noodle for suggesting the use of a cursor!!!
    Paul Young
    (Knowledge is power! Get some!)

  15. #15
    Join Date
    Jan 2003
    Location
    Chicago, IL, USA
    Posts
    8


    Whatever it takes to make it work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •