getstr(str)函数 之 在SQL语句中,如何判断汉字和字符?

----------------------------------------------------------------------------------------------------------------------
--创建    Function :dbo.getstr(str)
--功能：提取字符串（'-' 或 '\' 或 '空格' 或 '汉字' 字符前面的部分的字符串）
--时间：2012-3-26
--作者:   <李美宏>
----------------------------------------------------------------------------------------------------------------------
drop function dbo.getstr
create function dbo.getstr
(
 @str nvarchar(256)
)
returns nvarchar(256)
as
begin
 return
 (
  case
   when patindex('%-%', @str)>0 then dbo.getstr(substring(@str, 1, patindex('%-%', @str)-1))
   when patindex('%/%', @str)>0 then dbo.getstr(substring(@str, 1, patindex('%/%', @str)-1))
   when patindex('% %', @str)>0 then dbo.getstr(substring(@str, 1, patindex('% %', @str)-1))
   when patindex('%[吖-座]%',@str)>0 then dbo.getstr(substring(@str, 1, patindex('%[吖-座]%', @str)-1))
   else @str
  end
 )
end

----------------------------------------------------------------------------------------------------------------------
oracle中：

------------

SQL> create table test(a varchar2(10));

Table created.

SQL> insert into test values('鸟');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values('深刻');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values('aaa');

1 row created.

SQL> insert into test values('bbb');

1 row created.

SQL> commit;

Commit complete.

SQL> select a from test;

A
----------
鸟
深刻
aaa
bbb

SQL> select a from test where asciistr(a) like '%\%';

A
----------
鸟
深刻

SQL> select a from test where asciistr(a) not like  '%\%';

A
----------
aaa
bbb

 

select a,asciistr(a) from test where asciistr(a) like '%\%';

A          ASCIISTR(A)
---------- --------------------------------------------------
鸟         \FFFD\FFFD
深刻       \FFFD\FFFD\FFFD\FFFD

 

----------------------------------------------------------------------------------------------------------------------

sql中：

--------

declare @t table([Name] nvarchar(10))
insert @t select '好的啊!'
insert @t select '12345好的'
insert @t select '123'
insert @t select 'sdff'
select * from @t where PATINDEX('%[吖-座]%',[name])>0
/*
Name      
----------
好的啊!
12345好的

（所影响的行数为 2 行）*/

------------------------------------------------------------

PATINDEX('%[吖-座]%',[name])>0