Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL command (SQL server 7)

    Wasnt sure where to put this question, but VB users sometimes know a bit of SQL:

    Just a quick "know it or you dont question".

    Is there a command to count the number of instances of a specified character or string in a field?

    Thought i'd ask before I spend the time writing my own, however, if I do ill post it here.
    Any help would be much appreciated.

    Additional: Saying that, I can't seem to find the sql equivalent of the instr command to make my own function either!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL command (SQL server 7)

    Not sure if SQL 7 supports includes this function, but SQL 2000 has the CHARINDEX(StringToFind,Column[,StartIndex]) function which does much the same thing as Instr().
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL command (SQL server 7)

    Thanks Wendel, it works great.

    So far i've been unable to find a command to return the number of instances so i'm going to make a user defined function that uses CharIndex in a loop to count them one by one. Will post it if I succeed.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL command (SQL server 7)

    SOLUTION:

    SQL function. In a view, call it using Cinstances(fieldname,"searchtext") as NumberOfInstances.
    Might need a db. in front of Cinstances but I can't remember.
    This returns the number of times the search criteria appears in each row of the fieldname column.

    CREATE FUNCTION CInstances(@VarData as varchar(35), @searchstr as varchar(35))
    RETURNS integer AS
    BEGIN
    DECLARE @result AS integer, @pos1 as integer, @VarDataLen as integer
    Set @VarDataLen = len(@VarData)
    SET @Result = 0
    set @pos1 = 1
    while @pos1 < @VarDataLen
    BEGIN
    if charindex(@searchstr,@vardata ,@pos1) >0
    Begin
    set @pos1 = @pos1 + charindex(@searchstr,@vardata ,@pos1)
    set @result = @result +1
    end
    else set @pos1 = @vardatalen
    END
    return @result
    END

Posting Permissions

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