query with string manipulations

Question

I need a query for the following problem. Table1 specifies the mandatory input fields. I will fetch the fields with inactive = 'No' and mandt field = 'yes'

So i have 4 records with fields as sev,sev1,cde,frt.

Table1:

Fields                 Inactive              mandt_field

sev                     no                      yes
sev1                    no                      yes
sev2                    yes                     yes
abd                     no                       no
cde                     no                      yes
frt                     no                      yes

Table 2 has data similar to this

concession           add_fields

TH-123               -sev*yes-sev1*no-sev2*yes
Th-234               -sev*yes-sev1*yes-cde*yes-frt*no
Th-345               -sev*yes-cde*yes-frt*no
TH-456               -cde*no-frt*no
Th-012               -sev*no-sev1*no-cde*no-frt*no
Th-451               -frt*yes
TH-900               -sev2*no

Now i need records which does not have the above 4 fields in add_fields. output should return the following records :- TH-123,Th-345,TH-456,Th-451,TH-900.

These 4 records does not have all 4 fields that we have retrieved from the previous table (sev,sev1,cde,frt).

The no. of the fields resulting from table1 may vary..As these are from a table data...so we may have (sev,sev1,cde,frt....)

Answer 1

To answer your original question

SELECT DISTINCT concession
FROM Table2
INNER JOIN Table1 ON Table2.add_fields NOT LIKE '%-' + Table1.Fields + '*%'
WHERE Inactive='no' AND mandt_field='yes'

Following on from the comments though add_fields seems to contain a list of items. That in turn contains pairs of codes and yes/no values. I suggest restructuring your table2 as follows. This will put it into first normal form.

Putting it into first normal form will make updates, and searches easier without having to parse every string each time to break it into its constituent items. It will also allow you to apply integrity constraints to your data.

concession    code    YesNo
----------------------------
TH-123        sev       yes
TH-123        sev1      no
TH-123        sev2      yes
Th-234        sev       yes
....

Answer 2

Short answer: Probably but you don't want to try.

Instead, create a third table from table two which contains the same data but in a form which you can use. This means to split the field add_fields into columns so you can use a join against table Table1

Long answer: SQL is touring complete, so you can write any program in it (mandelbrot set in T-SQL). But that gets complex quickly, so you really don't want to do it.