JimmyJimm JimmyJimm - 7 months ago 14
SQL Question

Extract values which not ends by specific words

Have table with some data. Could looks 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 see for some of them we have also records with romanian numbers in convention
<name><space><roman number>


For instance we see there are
"Gelb", "Weiß my color" and "Schwarz"
and there also records for them in romanian convention and for some not like Grau.

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

Romanian numbers could be always at the end like:
<name><space><romaniannumber>


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><anyromaniancharacter>'


P.S I cannot change structure of 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