Note: For the second value, you have to use both of the delimiter locations to substring the value. Use that as a SUBSTR point for the second and third values, and you're all set. Use the position of the first delimiter as the starting point for finding the second delimiter. Use the index of the first delimiter to specify a starting point: Note that LOCATE allows specifying a start position: >-LOCATE(search-string,source-string-+-+-+-+-)-><
Use the same concepts as the first scenario, but you have to determine the index of the second delimiter. Scenario: A string value with three components separated by a delimiter LOCATE is a bit more powerful because it allows you to specify a start position, which would be helpful if you had more than one delimiter.įor the first part, start your substring at position 1, up to the character before the delimiter (delimiter position - 1): SUBSTR('CHG-FFH', 1,LOCATE('-','CHG-FFH')-1) as FIRST_PARTįor the second part, start your substring at the position after delimiter index (delimiter position + 1), and get the rest of the String: SUBSTR('CHG-FFH', LOCATE('-','CHG-FFH')+1) as SECOND_PART NOTE: DB2 provides two functions which can be used for this: POSITION (or POSSTR), and LOCATE (or LOCATE_IN_STRING). With only two parts, you need to find the position of the delimiter, and then substring before and after it by using the position before and after it in a substring function. Scenario: A string value with two components separated by a delimiter In your example, you have two, so I'll start with that assumption, and then afterwards comment on how you would deal other situations. If your string had more than one dash in it, would you want to split it into three parts? Four? So the first step is to note if your data is determinate - if it has a specific number of components that you want to split apart. The reason is likely that it's not an implicitly scalar function. Substr(input, first_pos+1,length) as partĪlternatively, you can see a different approach here at this answer: Split a VARCHAR in DB2 to retrieve a value inside.ĭB2, along with other relational databases do not provide a single function to accomplish this. Then length(input) - LOCATE_IN_STRING(input, '-',1, part_number - 1, OCTETS)Įlse LOCATE_IN_STRING(input, delimiter,1, part_number, OCTETS) - LOCATE_IN_STRING(input, delimiter,1, part_number-1, OCTETS) - 1 When LOCATE_IN_STRING(input, delimiter,1, part_number, OCTETS) = 0Īnd LOCATE_IN_STRING(input, delimiter,1, part_number-1, OCTETS) = 0 CREATE FUNCTION SPLITTER (input VARCHAR(4000), delimiter CHAR, part_number INTEGER)Įlse LOCATE_IN_STRING(input, delimiter,1, part_number-1, OCTETS)Ĭase when part_number = 1 then LOCATE_IN_STRING(input, delimiter,1, part_number, OCTETS) - 1
, SUBSTR('CHG-FFH', LOCATE('-','CHG-FFH')+1) as SECOND_PARTīONUS! If you do this often, create a user defined function to do it dynamically. Any ideas as to how I can achieve this?ĮDIT: Forgot to mention that this is Cognos 8.4.Delimiter, and then substring using it as the starting point, and a calculated length. That has not given me the results I want. I've tried pagination at the region and location level. If the whole region subgroup (including summary) doesn't fit on the remainder of the page, I want it to go to the next page. For example: | Val 1 | Val 2ĭivision 1 | Region 1 | Location 1 | 5.00 | 3.00ĭivision 2 | Region 1 | Location 1 | 3.00 | 12.85Ĭurrently, Cognos is breaking the page in the middle of the Division 1/Region 2 subgroup. I am reporting off of a cube into a crosstab with the results being summarized.
0 kommentar(er)
