Extract values which do not end by specific words

Question

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 1

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

Answer 2

Here is my solution:

First, generate a list of Roman Numerals up to a specified limit. Then, extract the last word from your table and check if it exists in the list of Roman Numerals:

ONLINE DEMO

;WITH E1(N) AS( 
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), 
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), 
CteTally(N) AS(
    SELECT TOP(1000)  -- Replace value inside TOP for MAX roman numbers
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteRoman(N, Roman) AS(
    SELECT *
    FROM CteTally t
    CROSS APPLY(
        SELECT
            REPLICATE('M', t.N/1000)
             + REPLACE(REPLACE(REPLACE(
                  REPLICATE('C', t.N%1000/100),
                  REPLICATE('C', 9), 'CM'),
                  REPLICATE('C', 5), 'D'),
                  REPLICATE('C', 4), 'CD')
             + REPLACE(REPLACE(REPLACE(
                  REPLICATE('X', t.N%100 / 10),
                  REPLICATE('X', 9),'XC'),
                  REPLICATE('X', 5), 'L'),
                  REPLICATE('X', 4), 'XL')
             + REPLACE(REPLACE(REPLACE(
                  REPLICATE('I', t.N%10),
                  REPLICATE('I', 9),'IX'),
                  REPLICATE('I', 5), 'V'),
                  REPLICATE('I', 4),'IV')
    ) r(a)
),
CteLastWord AS(
    SELECT *,
        LastWord = CASE 
                    WHEN CHARINDEX(' ', Name) = 0 THEN Name
                    ELSE REVERSE(LEFT(REVERSE(Name), CHARINDEX(' ', REVERSE(Name)) - 1))
                   END
    FROM MyTable
)
SELECT
    id, Name    
FROM CteLastWord w
WHERE 
    NOT EXISTS(
        SELECT 1 
        FROM CteRoman
        WHERE
            Roman = w.LastWord              
    )
ORDER BY w.Id

Reference:

• Convert Number to Roman Numerals in Sql Server

Answer 3

I would do this. First of all create the ToRomanNumerals function from here

And now create the numbers table with the roman numbers (I have created it from 1..100) and then use the LEFT(CHARINDEX) to remove the roman numerals from the name, like this:

DROP TABLE #Table1

CREATE TABLE #Table1
    ([ID] int, [name] varchar(17))
;

INSERT INTO #Table1
    ([ID], [name])
VALUES
    (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')
;

--select name, patindex('%M{0,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})', name) from #Table1
--select name, patindex('% [IVXLC]%', name) from #Table1

;with n as 
(select 1 as n
union all
select n.n+1 as n
from n where n < 100),
nr as
(select n, dbo.ToRomanNumerals(n) r
from n)
select name, nr.r, COALESCE(LEFT(name, PATINDEX('% ' + nr.r, name)), name) from #Table1 t
LEFT JOIN nr ON t.name LIKE '% ' + nr.r

Result:

name              r        
----------------- -------- -----------------
Gelb              NULL     Gelb
Schwarz           NULL     Schwarz
Weiß my color     NULL     Weiß my color
Grau              NULL     Grau
Gelb I            I        Gelb 
Gelb II           II       Gelb 
Gelb III          III      Gelb 
Gelb IV           IV       Gelb 
Schwarz I         I        Schwarz 
Schwarz II        II       Schwarz 
Schwarz III       III      Schwarz 
Schwarz IV        IV       Schwarz 
Schwarz V         V        Schwarz 
Schwarz VI        VI       Schwarz 
Weiß my color III III      Weiß my color 
Weiß my color IV  IV       Weiß my color 
Weiß my color V   V        Weiß my color 
Weiß my color VI  VI       Weiß my color 

(18 row(s) affected)

Answer 4

I hope this solves your issue. Add one more column in your table which contains only Roman Number of the name, if no Roman number save empty string.

select distinct left(NAME,LEN(NAME)-CHARINDEX(RomanNumberColumn,REVERSE(NAME))) FROM TABLE

Answer 5

this should be work:

select distinct ID, name from YourTable
where right (name,charindex(' ',REVERSE(name))) not like '%[IVXLCDM]%' COLLATE SQL_Latin1_General_CP1_CS_AS

Where checks only last word in NAME column if it contains X V I L characters.

Answer 6

Replace cte with your table name, same to columns. I use id for numeric code and name for names.

SELECT DISTINCT c.id,
                t.name 
FROM (
    SELECT  c1.name,
            DENSE_RANK() OVER (PARTITION BY c2.name ORDER BY c1.name) as DR
    FROM cte c1
    LEFT JOIN cte c2 
        ON c2.name LIKE c1.name + '%' 
) as t
INNER JOIN cte c
    ON c.name = t.name 
WHERE t.DR = 1

Output:

id          name
----------- -----------------
7           Gelb
8           Schwarz
9           Wei? my color
10          Grau

(4 row(s) affected)