Search specific format value in database field

I have to search for specific format values in a database field. Value format as below:

ABC 1167:YYYY
ABC 45870:YYYY
ABC 7654:YYYY
ABC 45076:YYYY

YYYY– could be a year i.e. 2018, 2019

I tried to use Format() function as well as tried with RegEX but it’s not getting results as expected. Any help would be appreciated!

Answer

Here I am assuming that atleast 4 digits will follow ‘ABC ‘ and post the digits you will have : and followed by years ‘2018’ or ‘2019’

DECLARE @table TABLE(charValue VARCHAR(100));
INSERT INTO @table VALUES
('ABC 1167:2018'),
('ABC 45870:2019'),
('ABC 7654:2018'),
('ABC 45076:2019');

SELECT * FROM @table
WHERE charValue LIKE 'ABC [0-9][0-9][0-9]%:201[8-9]'

+----------------+
|   charValue    |
+----------------+
| ABC 1167:2018  |
| ABC 45870:2019 |
| ABC 7654:2018  |
| ABC 45076:2019 |
+----------------+