JimmyJimm JimmyJimm - 7 months ago 9
SQL Question

Extract values which do not end by specific words

I have a table with some data. It could look for example like this:

7 Gelb
8 Schwarz
9 Weiß my color
10 Grau
16 Gelb I
17 Gelb II
18 Gelb III
19 Gelb IV
27 Schwarz I
28 Schwarz II
29 Schwarz III
30 Schwarz IV
31 Schwarz V
32 Schwarz VI
39 Weiß my color III
40 Weiß my color IV
41 Weiß my color V
42 Weiß my color VI


As you can see, in some records we have roman numbers in convention
<name><space><roman number>


For instance, there are "Gelb", "Weiß my color" and "Schwarz" and there are also records for them in roman convention. For some, like "Grau", there are no duplicates.

So there will be record with unique color name without a roman number e.g record "Grau" and in the table it could contain or not some records with it and roman numbers for it.

Roman numbers would be always at the end like:
<name><space><romannumber>


My goal is only to get unique names. So out of example i want to extract only:

7 Gelb
8 Schwarz
9 Weiß my color
10 Grau


How can i achieve that?

I started with this, would it be enough?

Select Id, Name From MyTable Where Name Not Like = '%<space><anyromancharacter>'


I cannot change structure of the database.

Answer

update

select * from dbo.test
Where value not Like '%[MDILXV]_' Collate SQL_Latin1_General_CP1_CS_AS

Step 1 :

select * from dbo.test

    id  value
    1   Gelb
    2   Gelb I
    3   Weiß my color III
    4   Weiß my color

When i give

   select * from dbo.test
    Where value not Like '%[IXLV]' Collate SQL_Latin1_General_CP1_CS_AS

id  value
1   Gelb
4   Weiß my color
Comments