Sql Server和Oracle10g如何根据某列的中文的首字母查询？ 如何查询首字母为‘a’的记录 sql server

sql\u4e2d\u600e\u4e48\u6839\u636e\u6c49\u5b57\u7684\u62fc\u97f3\u9996\u5b57\u6bcd\u67e5\u8be2

-- SQLServer:---\u6d4b\u8bd5\u6570\u636e---if object_id('[pactinfo]') is not null drop table [pactinfo]gocreate table [pactinfo]([ID] int,[pactname] varchar(4))insert [pactinfo]select 1,'\u6b63\u5e38' union allselect 2,'\u4e2d\u56fd' union allselect 3,'\u505a\u996d' union allselect 4,'\u52a0\u53d1' ---\u5f15\u7528\u524d\u8f88\u4eec\u7684\u4e00\u4e2a\u51fd\u6570---create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @strlen int,@re nvarchar(4000) declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) insert into @t(chr,letter) select '\u5416 ', 'A ' union all select '\u516b ', 'B ' union all select '\u5693 ', 'C ' union all select '\u5491 ', 'D ' union all select '\u59b8 ', 'E ' union all select '\u53d1 ', 'F ' union all select '\u65ee ', 'G ' union all select '\u94ea ', 'H ' union all select '\u4e0c ', 'J ' union all select '\u5494 ', 'K ' union all select '\u5783 ', 'L ' union all select '\u5452 ', 'M ' union all select '\u62cf ', 'N ' union all select '\u5662 ', 'O ' union all select '\u5991 ', 'P ' union all select '\u4e03 ', 'Q ' union all select '\u5465 ', 'R ' union all select '\u4ee8 ', 'S ' union all select '\u4ed6 ', 'T ' union all select '\u5c72 ', 'W ' union all select '\u5915 ', 'X ' union all select '\u4e2b ', 'Y ' union all select '\u5e00 ', 'Z ' select @strlen=len(@str),@re= ' ' while @strlen> 0 begin select top 1 @re=letter+@re,@strlen=@strlen-1 from @t a where chr <=substring(@str,@strlen,1) order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 end return(@re) end ---\u67e5\u8be2---select * from [pactinfo]where left(dbo.f_GetPy(pactname),1)='Z'---\u7ed3\u679c---ID pactname ----------- -------- 1 \u6b63\u5e382 \u4e2d\u56fd3 \u505a\u996d\uff08\u6240\u5f71\u54cd\u7684\u884c\u6570\u4e3a 3 \u884c\uff09

\u67e5\u8be2\u9996\u5b57\u6bcd\u4e3a\u2018a\u2019\u7684\u8bb0\u5f55\u4f7f\u7528\u5230\u7684\u662f\uff0csql server\u6a21\u7cca\u67e5\u8be2\u8bed\u53e5\u3002
\u4e00\u3001\u6a21\u7cca\u67e5\u8be2\u4f7f\u7528\u5230LIKE \u64cd\u4f5c\u7b26\uff0c\u7528\u4e8e\u5728 WHERE \u5b50\u53e5\u4e2d\u641c\u7d22\u5217\u4e2d\u7684\u6307\u5b9a\u6a21\u5f0f\u3002
\u4e8c\u3001SQL LIKE \u64cd\u4f5c\u7b26\u8bed\u6cd5
SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern\u6ce8\uff1apattern\u4f1a\u4f7f\u7528\u5230\u2018%\u2019\u901a\u914d\u7b26\uff0c\u8868\u793a\u4efb\u610f\u5b57\u7b26\u6216\u5b57\u7b26\u4e32
\u4e09\u3001\u5b9e\u4f8b\u6f14\u793a\uff1a
1\u3001\u5b9e\u4f8b\u8868\u683c\uff1aexp_test

2\u3001\u67e5\u8be2\u9996\u5b57\u6bcd\u4e3a\u2018a\u2019\u7684\u8bb0\u5f55,\u68c0\u7d22\u6761\u4ef6\u2018a%\u2019,\u5373\u4ee5'a'\u4f5c\u4e3a\u9996\u5b57\u6bcd\u7684\u5b57\u7b26\u4e32

3\u3001\u67e5\u8be2\u5305\u542b\u5b57\u6bcd\u2018a\u2019\u7684\u8bb0\u5f55,\u68c0\u7d22\u6761\u4ef6\u2018%a%\u2019

4\u3001\u67e5\u8be2\u5b57\u6bcd\u2018r\u2019\u7ed3\u5c3e\u7684\u8bb0\u5f55,\u68c0\u7d22\u6761\u4ef6\u2018%r\u2019

这个函数可以把汉字串变成首字母串
复制运行后f_GetPy函数就能用了

GO

/****** Object: UserDefinedFunction [dbo].[f_GetPy] Script Date: 2013/9/12 8:20:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[f_GetPy](@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖 ', 'A ' union all select '八 ', 'B ' union all
select '嚓 ', 'C ' union all select '咑 ', 'D ' union all
select '妸 ', 'E ' union all select '发 ', 'F ' union all
select '旮 ', 'G ' union all select '铪 ', 'H ' union all
select '丌 ', 'J ' union all select '咔 ', 'K ' union all
select '垃 ', 'L ' union all select '呒 ', 'M ' union all
select '拏 ', 'N ' union all select '噢 ', 'O ' union all
select '妑 ', 'P ' union all select '七 ', 'Q ' union all
select '呥 ', 'R ' union all select '仨 ', 'S ' union all
select '他 ', 'T ' union all select '屲 ', 'W ' union all
select '夕 ', 'X ' union all select '丫 ', 'Y ' union all
select '帀 ', 'Z '
select @strlen=len(@str),@re= ' '
while @strlen> 0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr <=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end

GO

这个以前我也研究过，需要建立函数，然后导入汉字字典。大体是这个意思。

我只是来看看不说话的

扩展阅读：microsoft authenticator ... microsoftedge浏览器 ... microsoft visual ... sql server和mysql哪个好 ... sql server 2019 ... sql server能不能删 ... microsoft sql server ... sql server 2008 ... spring boot ...