asked on

SQL Server function parse

I need to create a function FN_getstrListCount to return the number of items in the variable @stringlist separated by comma.

declare @stringlist nvarchar(100)
set @stringlist = 'E,F,G ,3,IK'
Any help would be appreciated

Thanks

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

You can try like this.

declare @stringlist nvarchar(100)
set @stringlist = 'E,F,G ,3,IK'

select len(@stringlist)-len(replace(@stringlist,',',''))+1

virtuadept

Well as long as you know comma is not going to be the last character of the string, then you know that the count of things between commas will be number of commas + 1.

SELECT @comma_count = LEN(@stringlist) - LEN(REPLACE(@stringlist,',',''))

What that does is compare lenght of the string versus length of the string without the commas so you know how many characters in the string are commas.

virtuadept

If you need to evaluate the actual values between the commas, I have a function that will return a table of the values between specificed delimeter.

jknj72

ASKER

thanks jimhorn

Jim Horn

Thanks for the grade. Good luck with your project. -Jim