question

kayor avatar image
kayor asked

case when join

Hi,

I'd like to join on 2 fields(see image below: child name and produce name 1). i need a case statement to join on green brussels after the semicolon and if there's no semicolon, join on brussels sprouts before the semicolon.


Here's my sql syntax so far:

inner join [srp].[ProduceChildItems] as pci

on c.produce=

SUBSTRING(pci.ChildName, 0, charindex(':', pci.ChildName, 0))--before character

1644430861474.png

Any help will be appreciated.

,

Hi,

I'd like to join on 2 fields(see image below: child name and produce name 1). i need a case statement to join on green brussels after the semicolon and if there's no semicolon, join on brussels sprouts before the semicolon.


Here's my sql syntax so far:

inner join [srp].[ProduceChildItems] as pci

on c.produce=

SUBSTRING(pci.ChildName, 0, charindex(':', pci.ChildName, 0))--before character

1644430861474.png

Any help will be appreciated.

joinstring-functionsql2005
1644430861474.png (5.4 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chris120 avatar image
chris120 answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kayor avatar image
kayor answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chris120 avatar image
chris120 answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.