question

Try joining on

c.Produce =

RIGHT(pci.ChildName, LEN(pci.ChildName) - CHARINDEX(';', pci.ChildName))

CHARINDEX will return 0 if it doesn't find the searched for character, so this will either return the whole string (if there is no ';' character) or just the substring to the right of the ';' character.

of course this should work but its not. its returning the produce names without a semicolon and child name.

Here is an example of what the data in the child name field looks like: potato: russet
Here is an example of what the produce name field looks like: russet

In the syntax you gave above, its not returning anything based on the example above. its only returning rows with exact matching names(without semicolon and child name). Example of returning data: ginger=ginger

Might be a leading space issue, if there is a space after the semicolon. If that's the issue, you'd want to wrap my code in a LTRIM() or TRIM() function. Or I've just noticed that you're using a colon (:) in all of your examples, whereas I'm using a semicolon (;), so that might be the issue if you copied and pasted my code exactly.

I'm also not sure if I'm applying my code to the right column. In your examples you seem to be trying to manipulate the ChildName column to extract the child product, so that's what I've continued with my code, but I've spotted that your example text actually has Product Name 1 as the column with the parent and child product.

So, if you could provide a bit more information (ideally full table definitions and some example data) I could come up with something that would work for sure, but without that, these are my best guesses.