提取汉字方法

create table tb(col char(20))
insert into tb values('照片21cm')
insert into tb values('彩图12cm')
insert into tb values('有光盘')
insert into tb values('21cm')
insert into tb values(null)
go

create function f_get_c(@oldstr varchar(100)) returns varchar(100)
as
  begin
  declare @i int
  set @i = 1
  while @i <= len(@oldstr)
  if substring(@oldstr, @i, 1) like('[^吖-咗]')
    set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
  else
  set @i = @i +1
  return @oldstr
end
go

create function f_get_e(@oldstr varchar(100)) returns varchar(100)
as
  begin
  declare @i int
  set @i = 1
  while @i <= len(@oldstr)
  if substring(@oldstr, @i, 1) like('[^a-z,A-Z,0-9]')
    set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
  else
  set @i = @i +1
  return @oldstr
end
go

select dbo.f_get_c(col) col_c , dbo.f_get_e(col) col_e from tb where col like('%[^吖-咗]%') or  col like('%[^a-z,A-Z,0-9]%')
union all
select col1 = null,col2 = null from tb where col is null

drop table tb
drop function f_get_e
drop function f_get_c

他的作用是做一个通陪符，意思就是要查找的字符串应该是以中文开头