Извлечение значений, которые не заканчиваются конкретными словами

Ответ 1

Обновление

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

Шаг 1:

select * from dbo.test

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

Когда я даю

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

id  value
1   Gelb
4   Weiß my color

Ответ 2

Вот мое решение:

Сначала создайте список римских цифр до указанного предела. Затем извлеките последнее слово из своей таблицы и проверьте, существует ли он в списке римских цифр:

ОНЛАЙН-ДЕМО

;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

Ссылка:

• Преобразование числа в римские цифры на сервере Sql

Ответ 3

Я бы сделал это. Прежде всего создайте функцию ToRomanNumerals из здесь

И теперь создайте таблицу чисел с римскими номерами (я создал ее с 1..100), а затем воспользуюсь LEFT (CHARINDEX), чтобы удалить римские цифры из имени, например:

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

Результат:

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)

Ответ 4

Надеюсь, это решит вашу проблему. Добавьте еще один столбец в вашей таблице, который содержит только римский номер имени, если номер не сохранен в пустой строке.

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

Ответ 5

Это должна быть работа:

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

Where проверяет только последнее слово в столбце NAME, если оно содержит символы X V I L.

Ответ 6

Замените cte своим именем таблицы, то же, что и в столбцах. Я использую id для числового кода и name для имен.

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

Вывод:

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

(4 row(s) affected)