我有一个字段是身份证字段，现在想进行找错处理
第一步：
查询身份证字段中超过2个非数字的值
如
110227197001020001
11022719700102000X
11022719700102000Y
1102271970010200XX
11022719700102X00X
110227197001020Y0Y
结果
1102271970010200XX
11022719700102X00X
第二步
查询不能进行年月日拆分的，因为我们都可以通过身份证获取出生年月日，所以没能获取的都是错误的
谢谢
------解决方案--------------------
- SQL code
declare @t table([col] varchar(18))insert @tselect '110227197001020001' union allselect '11022719700102000X' union allselect '11022719700102000Y' union allselect '1102271970010200XX' union allselect '11022719700102X00X' union allselect '110227197001020Y0Y'select * from @t-- 长度不等于18 ，也可以判断一下15where 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------------------1102271970010200XX11022719700102X00X110227197001020Y0Y*/
------解决方案--------------------
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 tgocreate table test(id varchar(20))goinsert into testselect '110227197001020001' union allselect '11022719700102000X' union allselect '11022719700102000Y' union allselect '1102271970010200XX' union allselect '11022719700102X00X' union allselect '110227197001020Y0Y'go--第一步 需要用function来实现if object_id('dbo.fn_validateID') is not null drop function dbo.fn_validateIDgocreate function dbo.fn_validateID(@s nvarchar(20))returns intasbegin declare @i int set @i=0 while PATINDEX('%[^0-9]%',@s) > 0 begin set @s = STUFF(@s,PATINDEX('%[^0-9]%',@s),1,N'') set @[email protected]+1 end return @iendselect id from( select id,dbo.fn_validateID(id) cnt from test) twhere t.cnt>1--第二步select * from testwhere 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 tblCardGOCreate Table tblCard(CardID Varchar(18), CharVaild Bit, DateVaild Bit, IsVaild Bit)GOInsert 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 GetCharInStrCountGOCreate Function GetCharInStrCount(@Value Varchar(100))Returns IntasBegin While Patindex('%[^a-z]%', @Value) > 0 Set @Value=STUFF(@Value,Patindex('%[^a-z]%',@Value),1,'') Return Len(@Value)EndGo--检查字母个数是否超过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