我假设只有中文和非中文的情况,先创建函数:
--创建函数
create function [dbo].[m_getchinese]
(
@chinese nvarchar(max)
)
returns varchar(100)
as
begin
while patindex('%[^吖-咗]%',@chinese) > 0
begin
set @chinese = stuff(@chinese,patindex('%[^吖-咗]%',@chinese),1,N'');
end
return @chinese
end
go然后:
DECLARE @STR VARCHAR(20);
--SET @STR='AAAAABBBBBCCCCCDDDDDEEEEE'
SET @STR='一a一b一c二d二e三f三g三'SELECT CASE WHEN [dbo].[m_getchinese](@str) IS NULL THEN SUBSTRING(@str,1,20) WHEN [dbo].[m_getchinese](@str) Is NOT NULL THEN SUBSTRING(@str,1,10) END
--创建函数
create function [dbo].[m_getchinese]
(
@chinese nvarchar(max)
)
returns varchar(100)
as
begin
while patindex('%[^吖-咗]%',@chinese) > 0
begin
set @chinese = stuff(@chinese,patindex('%[^吖-咗]%',@chinese),1,N'');
end
return @chinese
end
go然后:
DECLARE @STR VARCHAR(20);
--SET @STR='AAAAABBBBBCCCCCDDDDDEEEEE'
SET @STR='一a一b一c二d二e三f三g三'SELECT CASE WHEN [dbo].[m_getchinese](@str) IS NULL THEN SUBSTRING(@str,1,20) WHEN [dbo].[m_getchinese](@str) Is NOT NULL THEN SUBSTRING(@str,1,10) END
select case when patindex('%[吖-座]%',@str) >0
then SUBSTRING(@STR,1,10)
else SUBSTRING(@STR,1,20)
end
/*
AAAAABBBBBCCCCCDDDDD
*/SET @STR='一a一b一c二d二e三f三g三'select case when patindex('%[吖-座]%',@str) >0
then SUBSTRING(@STR,1,10)
else SUBSTRING(@STR,1,20)
end
/*
一a一b一c二d二e
*/
需要注意的是,我在繁体系统中,DATALENGTH测试简体字的长度居然是1,而且SubString取字符时会变成?号。