Post reply

How to find No. Of occurences in a string without Loop

  • June 17, 2004 at 6:46 am

    #102069

    Hi Everybody,

    Declare @Temp VarChar(100)

    Set @Temp = 'a, b, c, e, 3'

    In the above example i need to find out no of commas in @Temp variable.  I don't want the solution with a loop by using SP or Function.  Is there any readymade function in MSSQLServer to get the no of occurences ?  I am using MSSQLSERVER 2000.

    Please help me........

    Thanks in advance

    Bobby

  • June 17, 2004 at 9:33 am

    #510877

    There is no inherent function in sql.

    Even in vb you need to loop through the characters to perform the task.

    You are looking at writing a script using the 'substring' function to find each ocurrence and trap it soemwhere.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 18, 2004 at 4:35 am

    #510980

    Check out this link

    http://www.sqlservercentral.com/scripts/contributions/1099.asp

  • June 18, 2004 at 4:46 am

    #510981

    Thanks for your reply.

    Sorry,  If i use StringCount function that Stringcount function should exists in my database.  I don't want that.  Any System function available ?

     

    Bobby

  • June 18, 2004 at 5:36 am

    #510992

    Not really sure why u don't want a function or sp in your db, but try this

    Select Len('a,b,c,e,3') - len(Replace('a,b,c,e,3',',',''))

    this assumes that you don't have any embeded spaces in the string

  • June 18, 2004 at 6:30 am

    #511004

    Thanks a lot.  My PL said without using a any loop i should get the output.

    Thanks again.

    Bobby

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply