I am trying to run a query like this :
matches pattern 94042%;21555%;21988%.
So i have a list of about 200 numbers where i know what the first 5 characters are and then need to do a match pattern to pick up any character after the 5th character. I then need to put them in a list and not not one by one as BO is tellimg me to do.
is this possible in webi as i cannot write this in free sql so need to use the query filters in webi.
if i use “in list” instead of “matches pattern” it allows me to add the 200 numbers but it does not pick up all entries as i cannot pattern match after the 5th character.
Thnaks for the reply. So there is no solution for what I want to do. ie put the first 5 digits of 200 numbers in a list where it also picks up any characters after the 5th digit of all of those 200 numbers
The solution requires to create a condition in the universe. The condition would be something along the lines:
SUBSTR(table_name.column_name,1,5) IN @prompt('type prefixes of numbers you want','A',,multi,free)
Then you can type in the prompt many values - 5 characters long strings - and compare them against the first 5 characters of the table_name.column_name column.
If you are going to use an ever changing list of prefixes I would agree with Marek to have the substr built into the universe level. If the list is static though you may want to build it in at the database level as well as the universe level. This way if the list is ever updated then you can just have a DBA refresh the list for you. We are currently using the latter at my company for large list that are used for extended times.
(BOB member since 2003-11-27)
(BOB member since 2007-03-30)
(BOB member since 2011-08-21)