SQL 查询值中有2个非数字的值

Question

我有一个字段是身份证字段，现在想进行找错处理
第一步：
查询身份证字段中超过2个非数字的值
如
110227197001020001
11022719700102000X
11022719700102000Y
1102271970010200XX
11022719700102X00X
110227197001020Y0Y
结果
1102271970010200XX
11022719700102X00X
第二步
查询不能进行年月日拆分的，因为我们都可以通过身份证获取出生年月日，所以没能获取的都是错误的谢谢

Answer 1

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
*/

Answer 2

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

Answer 3

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
endselect 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

Answer 4

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

Answer 5

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'
GOIF OBJECT_ID('GetCharInStrCount') Is Not Null
  Drop Function GetCharInStrCount
GOCreate Function GetCharInStrCount(@Value Varchar(100))
Returns Int
as
Begin
  While Patindex('%[^a-z]%', @Value) > 0
    Set @Value=STUFF(@Value,Patindex('%[^a-z]%',@Value),1,'')
    
  Return Len(@Value)
End
Go--检查字母个数是否超过2个 1表示有效 0表示无效
Update tblCard Set CharVaild = CASE WHEN dbo.GetCharInStrCount(CardID) <= 1 THEN 1 ELSE 0 END 
--检查日期是否有效 1表示有效 0表示无效
Update tblCard Set DateVaild = CASE WHEN ISDATE(SUBSTRING(CardID, 7, 8)) = 1 THEN 1 ELSE 0 END
--检查整体字符串是否有效 1表示有效 0表示无效
Update tblCard Set IsVaild = CASE WHEN DateVaild=1 AND CharVaild=1 THEN 1 ELSE 0 ENDSelect * From tblCard

Answer 6

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 PATINDEX('%[a-z]%',[col]),* from @t WHERE PATINDEX('%[a-z]%',[col]) NOT IN(0,18)PATINDEX 找字母的位置不就可以了，
身份证的字母要么在最后一位要么没有字母