how to find if a record has roman numerals in SQL Server?

Question

I want to detect records from a table (SQL Server) if it has roman numerals.

Table has data as follows:

    Column A | Column B
-----------------------------
1            | AMC I XYZAQS
2            | ABC IV 2 XYZQWS
3            | ANR XVI ANCVP
4            | SWD POL 2#

So, the result will be the first 3 records

How would you know if a character is a roman numeral? The 4th record in your sample has an `L` which is a roman numeral as well, why don't you want it selected? — HoneyBadger, Feb 14 '17 at 11:02
And what's the surrounding language? Is `LIV` roman numerals or just the word "life" in Swedish? — Damien_The_Unbeliever, Feb 14 '17 at 11:03
I will consider that to be a roman numeral irrespective of the fact that it has real meaning in any other language. — SChowdhury, Feb 14 '17 at 11:03
The functions are not recognized in SQL Server. Functions such as DECODE, REGEXP_SUBSTR — SChowdhury, Feb 14 '17 at 11:05
http://stackoverflow.com/questions/267399/how-do-you-match-only-valid-roman-numerals-with-a-regular-expression — Robin Mackenzie, Feb 14 '17 at 11:08
SQL server doesn't have native regex support, you'll have to write it out yourself — HoneyBadger, Feb 14 '17 at 11:10
@RobinMackenzie: This is not specific to SQL Server. RegEx of this sort is not recognized in MS SQL Server — SChowdhury, Feb 14 '17 at 11:10
Create a table with all roman numerals, and join it using `like` would be an option. Won't perform very well, I think. — HoneyBadger, Feb 14 '17 at 11:13
@HoneyBadger: Thanks. That's the last resort. I'm here looking out if anyone can suggest any better solution — SChowdhury, Feb 14 '17 at 11:15

score 0 · Accepted Answer · answered Feb 14 '17 at 11:20

0

What about this?

DECLARE @dummy TABLE(A INT,B VARCHAR(100));
INSERT INTO @dummy VALUES
 (1,'AMC I XYZAQS')
,(2,'ABC IV 2 XYZQWS')
,(3,'ANR XVI ANCVP')
,(4,'SWD POL 2#');

WITH Casted AS
(
    SELECT A
          ,B
          ,CAST('<x>' + REPLACE((SELECT B AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML) InParts
    FROM @dummy
)
,Splitted AS
(
    SELECT A
          ,B
          ,x.value('.','nvarchar(max)') AS part 
    FROM Casted
    OUTER APPLY InParts.nodes('x') AS Parts(x)
)
SELECT * FROM Splitted
WHERE UPPER(part) NOT LIKE '%[0-9ABEFGHJKNOPQRSTUWYZ]%'

The result

A   B               part
1   AMC I XYZAQS    I
2   ABC IV 2 XYZQWS IV
3   ANR XVI ANCVP   XVI

This will not check any rules of roman numerals. It only checks for the existance of special characters

answered Feb 14 '17 at 11:20

Shnugo

66,100
9
53
114

1

Nice approach, but the result also includes invalid sequences of numerals, such as 'ixv' – devio Feb 14 '17 at 11:24
1

@devio. That is my last line :-) Well, checking for such rules *with T-SQL* is cutting a bread with a chain saw... – Shnugo Feb 14 '17 at 11:26
1

@devio - I applied a udf to convert those "Part"s to decimal number system and ignored whenever the function did not return valid numeric value. This way we can filter the result one step ahead. – SChowdhury Feb 14 '17 at 21:10

how to find if a record has roman numerals in SQL Server?

1 Answers1