Pickup the value from the 2nd set of string - MS SQL query
-
Hello,
I am trying to pick-up the value from the second set of strings. See attached photo.
There are more than two sets of values, which are separated by >
The logics are:
1. We will pick-up the THIRD set of value. For example, the value of "Business Management" or "Performance Capacity and Event Management"
2. However, if there is no third set of value, for example: Shared Service > Business Services. Select only the second set of value
I was able to fulfill the first logic with the following syntax:
SUBSTRING(LTRIM(SUBSTRING(Column1, ISNULL(NULLIF(CHARINDEX('>', Column1), 0), LEN(Column1)) + 1, LEN(Column1))),0,CHARINDEX('>', LTRIM(SUBSTRING(Column1, ISNULL(NULLIF(CHARINDEX('>', Column1), 0), LEN(Column1)) + 1, LEN(Column1))),0))(I tried to put the '>' but it changed to > instead. So, please treat it as a > sign. Thanks.)
However, the second one won't work because it lacks of the fourth set of values. It gave me the blank value, instead. See photo:
Please assist in writing the CASE statement on these logics. I got stuck at this moment.
Hopefully, you might be able to assist. Thanks again.
-
You're really not helping us to help you. My suggestion would be to use the DelimitedSplit8k which can be found here along with its explanation:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Having that, the query would be something like this:
SELECT Column1, ISNULL( MAX(CASE WHEN ItemNumber = 3 THEN Item END), MAX(CASE WHEN ItemNumber = 2 THEN Item END))
FROM SomeTable st
CROSS APPLY dbo.DelimitedSplit8K(Column1, '>') split
GROUP BY Column1;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply