Is there a sql syntax or function that will allow me to parse the length of a string out of a string?
i.e. fieldname animals: dogs_cats, horse_cats
I need to parse to the '_'
len(instr(animals, '_'))

Or do I need to perform a do while or loop of somekind?

Thanks,
C

The CHARINDEX function will return the starting position of a substring within a string.

The SUBSTRING function will return a substring starting at a given position for a given length.

You can combine these, as for example, to return a substring of your first animal delimited by a "_":
TSQL is NOT the place you should be doing character/string manipulation, though ...

An even more important question is why you have composite data like this in the first place. This violates first normal form, and will cause you untold grief going forward. You'll find it very messy to query against such data, and it's ridiculously difficult to do things like accurately count how many rows have 'cats' in their animals column, etc.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff, thanks for your reply. Unfortunately, i wasn't part of the design on this db and would never have recommended building it this way. My only alternative is to fix the problem - which I wasn't contracted to do or make it work somehow which I should be able to do and let them keep calling me back to resolve more of their data issues.

Thanks again for your concern & help on this.
I made your syntax work and I'm getting the results I need.
Rgrds,
C