Convert a Navision Filter to SQL where

Question

I have a field in table in next format 1_2..1_10|1_6|1_8| where 1_2..1_10 include 1_2, 1_3 and other.

How I can select data, where number = 1_3?

Answer 1

1st suggestion: Get rights to modify the db structure and figure out how to better store the Navision string.

2nd suggestion: CLR

I'll assume you are relatively comfortable with each of these concepts. If you aren't they are very well documented all over the web.

My approach would be to use a CLR function as there's going to be some high level things that are awkward in SQL that C# takes care of quite easily. The psuedo walk through would go something like this.

Implementation

1. Create a CLR funciton and implement it on the SQL server instance.
2. Using SQL resultset change the query to look for the returned value of the CLR function based on the navision filter value where "1_3".

CLR Function Logic

1. Create a c# function that takes in the value of the filter field and returns a string value.
2. The CLR function splits the filter field by the | char into a list.
3. Inside the CLR function create a second list. Iterate over the first list. When you find a ranged string split it on the ".." and manually add every available value between the range to the second list. When you find a value that isnt' ranged simply add it to the second list.
4. Join the contents of the second list together on the "|" charecter.
5. Return the joined value.

SQL Logic

SELECT Field1,Field2...CLRFunctionName(FilterValue) AS FixedFilterValue FROM Sometable WHERE FixedFilterValue LIKE '%1_3%';