declare @t table([col] varchar(18))
insert @t
select '110227197001020001' union all
select '11022719700102000X' union all
select '11022719700102000Y' union all
select '1102271970010200XX' union all
select '11022719700102X00X' union all
select '110227197001020Y0Y'

select * from @t
-- 长度不等于18 ，也可以判断一下15
where len([col])<>18 or
-- 最后一位不是数字和XY
(right(col,1) not in ('X','Y') AND isnumeric(right(col,1))=0) or
-- 前6位是数字
isnumeric(left(col,6))=0 or
-- 中间8位是时间
isdate(substring(col,7,8))=0 or
--时间后面3位是数字
isnumeric(substring(col,15,3))=0

/*
col
------------------
1102271970010200XX
11022719700102X00X
110227197001020Y0Y
*/

------解决方案--------------------
WITH t AS (SELECT '110227197001020001' a FROM dual UNION ALL
  SELECT '11022719700102000X' FROM dual UNION ALL
SELECT '11022719700102000Y' FROM dual UNION ALL
SELECT '1102271970010200XX' FROM dual UNION ALL
SELECT '11022719700102X00X' FROM dual UNION ALL
SELECT '110227197001020Y0Y' FROM dual )
SELECT t.a FROM t  
WHERE length(regexp_replace(lower(t.a),'[a-z]',''))<=length(t.a)-2

------解决方案--------------------
SQL code
if object_id('t') is not null drop table t
go
create table test(id varchar(20))
go
insert into test
select '110227197001020001' union all
select '11022719700102000X' union all
select '11022719700102000Y' union all
select '1102271970010200XX' union all
select '11022719700102X00X' union all
select '110227197001020Y0Y'
go
--第一步 需要用function来实现
if object_id('dbo.fn_validateID') is not null drop function dbo.fn_validateID
go
create function dbo.fn_validateID(@s nvarchar(20))
returns int
as
begin
    declare @i int
    set @i=0
    while PATINDEX('%[^0-9]%',@s) > 0
    begin
        set @s = STUFF(@s,PATINDEX('%[^0-9]%',@s),1,N'')
        set @i=@i+1
    end
    return @i
end

select id from
(
    select id,dbo.fn_validateID(id) cnt from test
) t
where t.cnt>1

--第二步
select * from test
where substring(id,7,4)<1900 or substring(id,7,4)>year(getdate())+1  or substring(id,11,2)<1 or substring(id,11,2)>12 or substring(id,13,2)<1 or substring(id,13,2)>31

------解决方案--------------------
WITH t AS (SELECT '110227197001020001' a FROM dual UNION ALL
  SELECT '11022719700102000X' FROM dual UNION ALL
SELECT '11022719700102000Y' FROM dual UNION ALL
SELECT '1102271970010200XX' FROM dual UNION ALL
SELECT '11022719700102X00X' FROM dual UNION ALL
SELECT '110227197001020Y0Y' FROM dual )
SELECT t.a FROM t  
WHERE length(regexp_replace(lower(t.a),'[a-z]',''))<=length(t.a)-2

------解决方案--------------------
第一步：替换所有字母为空，这样长度就会变小。
第二步：总长度减替换后的长度，如果长度小于2就是你要的数据了。

------解决方案--------------------
SQL code

IF OBJECT_ID('tblCard') Is Not Null
  Drop Table tblCard
GO
Create Table tblCard(CardID Varchar(18), CharVaild Bit, DateVaild Bit, IsVaild Bit)
GO
Insert Into tblCard(CardID)
Select '110227197001020001'
Union All Select '11022719700102000X'
Union All Select '11022719700102000Y'
Union All Select '1102271970010200XX'
Union All Select '11022719700102X00X'
Union All Select '110227197001020Y0Y'
Union All Select '11022719700133X00X'
Union All Select '11022719700132000Y'
GO

IF OBJECT_ID('GetCharInStrCount') Is Not Null
  Drop Function GetCharInStrCount
GO

Create Function GetCharInStrCount(@Value Varchar(100