1

Table:

create table tbl_prefix
(
    col_pre varchar
);

Records:

insert into tbl_prefix values
('Mr.'),('Mrs.'),('Ms.'),('Dr.'),
('Jr.'),('Sr.'),('II'),('III'),
('IV'),('V'),('VI'),('VII'),
('VIII'),('I'),('IX'),('X'),
('Officer'),('Judge'),('Master');

Expected output:

col_pre
----------
Mr.
Mrs.
Ms.
Dr.
Jr.
Sr.
Officer
Judge
Master

Try:

select *
from tbl_prefix
where col_pre ~ '[^a-zA-Z]'

Getting:

col_pre
----------
Mr.
Mrs.
Ms.
Dr.
Jr.
Sr.
MAK
  • 6,824
  • 25
  • 74
  • 131
  • 1
    What exactly is your question? What do you mean with "only character"? Why is `VI` or `V` not in the expected output - those are "characters" as well. –  Aug 13 '20 at 05:54

1 Answers1

3

One approach here might be to match any prefix which is not a Roman numeral:

SELECT *
FROM tbl_prefix
WHERE col_pre !~ '^M{0,4}(CM|CD|D?C{0,3})(XC|XL|L?X{0,3})(IX|IV|V?I{0,3})$';

screen capture from demo link below

Demo

The regex pattern used here for Roman numerals was gratefully taken from this SO question:

How do you match only valid roman numerals with a regular expression?

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360