在SQL数据库中，按照英文首字母对数据库中的汉字进行筛选

Question

在SQL数据库中，按照英文首字母对数据库中的汉字进行筛选
比如：我点了“J”从数据库中表索出“金山”，“建行”之类的数据

Answer 1

Create Function RmGetPY(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63
select @n = @n +1,@c = case chn when @chn then char(@n) else @c end from(
select top 27 * from (
select chn =
'吖' union all select
'八' union all select
'嚓' union all select
'咑' union all select
'妸' union all select
'发' union all select
'旮' union all select
'铪' union all select
'丌' union all select
'丌' union all select
'咔' union all select
'垃' union all select
'呒' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select
'屲' union all select
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
end

go

Create Function GetAllPY(@chn nvarchar(100))
returns varchar(30)
as
begin

declare @i int,@j int,@result varchar(100)
set @result=''
set @i=len(@chn)
set @j=1
while @j<=@i
begin
set @result = @result + dbo.RmGetPY(substring(@chn,@j,1))
set @j=@j+1
end
--将助记码限定在30个字母之内
select @result=(case when len(@result)>30 then left(@result,30) else @result end)
return @result
end

先加这两个函数，然后
select * from table where dbo.GetAllPY(字段) like 'J%'

Answer 2

select * from table order by [name] collate chinese_prc_cs_as_ks_ws