REGEX

  • 203
  • 0
  • 2020-01-30

REGEX,以下範例以身份証為例

if object_id('tempdb..#T') is not null
    drop table #T

select 'A123456789' str into #T
union all select '123456789'
union all select 'ABCDEFG'
union all select 'AA12345678'

--全為數字
select * from #T where str not like '%[^0-9]%' 

--全為英文字
select * from #T where str not like '%[^a-zA-Z]%' 

--第一碼為英文,後面為數字
select * from #T 
where left(str,1) not like '%[^a-zA-Z]%' 
  and substring(str,2,LEN(str)-1) not like '%[^0-9]%' 

--第一、二碼為英文,後面為數字				
select * from #T 
where (left(str,1) not like '%[^a-zA-Z]%' and substring(str,2,LEN(str)-1) not like '%[^0-9]%')
   or (left(str,2) not like '%[^a-zA-Z]%' and substring(str,3,LEN(str)-2) not like '%[^0-9]%')