请教mssql 拉丁字母怎么转换成正常英文字母?

sunshinebean 2018-10-28 11:24:04


GEOIP库里有很多拉丁字母,类似截图里这样的.(截图里只是搜了ā相关的)。
所有的拉丁字母可以在这里看到:http://www.runoob.com/charsets/ref-utf-latin-extended-a.html

有没有什么办法可以让select出来的结果是正常的英文字母?比如Trikāta->Trikata

我有尝试过根据拉丁字母表建一个表,一个个去替换...但是mssql好像不能识别拉丁字母的大小写..我用collate Chinese_PRC_CS_AS测试正常的英文字母是可以的,但是测试拉丁字母搜的明明是Ā,但是出来的结果都是ā
...全文
641 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
sunshinebean 2018-11-08
  • 打赏
  • 举报
 回复
引用 4 楼 mxbing1984 的回复:

--不知道是不是你要的
--1.建表
CREATE TABLE word
(
	[key] NVARCHAR(10),
	[code] INT,
	[value] VARCHAR(10)
)

--2.插入数据 
insert into word values(N'Ā',256,'A')
insert into word values(N'ā',257,'a')
insert into word values(N'Ă',258,'A')
insert into word values(N'ă',259,'a')
insert into word values(N'Ą',260,'A')
insert into word values(N'ą',261,'a')
insert into word values(N'Ć',262,'C')
insert into word values(N'ć',263,'c')
insert into word values(N'Ĉ',264,'C')
insert into word values(N'ĉ',265,'c')
insert into word values(N'Ċ',266,'C')
insert into word values(N'ċ',267,'c')
insert into word values(N'Č',268,'C')
insert into word values(N'č',269,'c')
insert into word values(N'Ď',270,'D')
insert into word values(N'ď',271,'d')
insert into word values(N'Đ',272,'D')
insert into word values(N'đ',273,'d')
insert into word values(N'Ē',274,'E')
insert into word values(N'ē',275,'e')
insert into word values(N'Ĕ',276,'E')
insert into word values(N'ĕ',277,'e')
insert into word values(N'Ė',278,'E')
insert into word values(N'ė',279,'e')
insert into word values(N'Ę',280,'E')
insert into word values(N'ę',281,'e')
insert into word values(N'Ě',282,'E')
insert into word values(N'ě',283,'e')
insert into word values(N'Ĝ',284,'G')
insert into word values(N'ĝ',285,'g')
insert into word values(N'Ğ',286,'G')
insert into word values(N'ğ',287,'g')
insert into word values(N'Ġ',288,'G')
insert into word values(N'ġ',289,'g')
insert into word values(N'Ģ',290,'G')
insert into word values(N'ģ',291,'g')
insert into word values(N'Ĥ',292,'H')
insert into word values(N'ĥ',293,'h')
insert into word values(N'Ħ',294,'H')
insert into word values(N'ħ',295,'h')
insert into word values(N'Ĩ',296,'I')
insert into word values(N'ĩ',297,'i')
insert into word values(N'Ī',298,'I')
insert into word values(N'ī',299,'i')
insert into word values(N'Ĭ',300,'I')
insert into word values(N'ĭ',301,'i')
insert into word values(N'Į',302,'I')
insert into word values(N'į',303,'i')
insert into word values(N'İ',304,'I')
insert into word values(N'ı',305,'i')
insert into word values(N'IJ',306,'IJ')
insert into word values(N'ij',307,'ij')
insert into word values(N'Ĵ',308,'J')
insert into word values(N'ĵ',309,'j')
insert into word values(N'Ķ',310,'K')
insert into word values(N'ķ',311,'k')
insert into word values(N'ĸ',312,'kra')
insert into word values(N'Ĺ',313,'L')
insert into word values(N'ĺ',314,'l')
insert into word values(N'Ļ',315,'L')
insert into word values(N'ļ',316,'l')
insert into word values(N'Ľ',317,'L')
insert into word values(N'ľ',318,'l')
insert into word values(N'Ŀ',319,'L')
insert into word values(N'ŀ',320,'l')
insert into word values(N'Ł',321,'L')
insert into word values(N'ł',322,'l')
insert into word values(N'Ń',323,'N')
insert into word values(N'ń',324,'n')
insert into word values(N'Ņ',325,'N')
insert into word values(N'ņ',326,'n')
insert into word values(N'Ň',327,'N')
insert into word values(N'ň',328,'n')
insert into word values(N'ʼn',329,'N')
insert into word values(N'Ŋ',330,'n')
insert into word values(N'ŋ',331,'eng')
insert into word values(N'Ō',332,'O')
insert into word values(N'ō',333,'o')
insert into word values(N'Ŏ',334,'O')
insert into word values(N'ŏ',335,'o')
insert into word values(N'Ő',336,'O')
insert into word values(N'ő',337,'o')
insert into word values(N'Œ',338,'OE')
insert into word values(N'œ',339,'oe')
insert into word values(N'Ŕ',340,'R')
insert into word values(N'ŕ',341,'r')
insert into word values(N'Ŗ',342,'R')
insert into word values(N'ŗ',343,'r')
insert into word values(N'Ř',344,'R')
insert into word values(N'ř',345,'r')
insert into word values(N'Ś',346,'S')
insert into word values(N'ś',347,'s')
insert into word values(N'Ŝ',348,'S')
insert into word values(N'ŝ',349,'s')
insert into word values(N'Ş',350,'S')
insert into word values(N'ş',351,'s')
insert into word values(N'Š',352,'S')
insert into word values(N'š',353,'s')
insert into word values(N'Ţ',354,'T')
insert into word values(N'ţ',355,'t')
insert into word values(N'Ť',356,'T')
insert into word values(N'ť',357,'t')
insert into word values(N'Ŧ',358,'T')
insert into word values(N'ŧ',359,'t')
insert into word values(N'Ũ',360,'U')
insert into word values(N'ũ',361,'u')
insert into word values(N'Ū',362,'U')
insert into word values(N'ū',363,'u')
insert into word values(N'Ŭ',364,'U')
insert into word values(N'ŭ',365,'u')
insert into word values(N'Ů',366,'U')
insert into word values(N'ů',367,'u')
insert into word values(N'Ű',368,'U')
insert into word values(N'ű',369,'u')
insert into word values(N'Ų',370,'U')
insert into word values(N'ų',371,'u')
insert into word values(N'Ŵ',372,'W')
insert into word values(N'ŵ',373,'w')
insert into word values(N'Ŷ',374,'Y')
insert into word values(N'ŷ',375,'y')
insert into word values(N'Ÿ',376,'Y')
insert into word values(N'Ź',377,'Z')
insert into word values(N'ź',378,'z')
insert into word values(N'Ż',379,'Z')
insert into word values(N'ż',380,'z')
insert into word values(N'Ž',381,'Z')
insert into word values(N'ž',382,'z')
insert into word values(N'ſ',383,'s')

--3.创建函数
CREATE FUNCTION fn_toEnglish(@Val nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
	DECLARE @Rst NVARCHAR(4000)=''
	DECLARE @I INT=1
	DECLARE @cnt INT= LEN(@Val)
	DECLARE @Tmp NVARCHAR(1)

	WHILE @I<=@cnt
	BEGIN
		SET @Tmp=SUBSTRING(@Val,@I,1)
		IF UNICODE(@Tmp) BETWEEN 256 AND 383
			 SELECT @Rst=@Rst+value FROM word WHERE code= UNICODE(@Tmp)
		ELSE 
		  SET @Rst=@Rst+@Tmp
		SET @I=@I+1
	END  

	RETURN @Rst
END  

--4.测试
SELECT dbo.fn_toEnglish(N'ĀāĂ㥹ĆćĈĉĊċČčĎďĐđĒēĔĕĖėĘęĚěĜĝĞğĠġĢģĤĥĦħĨĩĪīĬĭĮįİıIJijĴĵĶķĸĹĺĻļĽľĿŀŁłŃńŅņŇňʼnŊŋŌōŎŏŐőŒœŔŕŖŗŘřŚśŜŝŞşŠšŢţŤťŦŧŨũŪūŬŭŮůŰűŲųŴŵŶŷŸŹźŻżŽžſ')
--AaAaAaCcCcCcCcDdDdEeEeEeEeEeGgGgGgGgHhHhIiIiIiIiIiIJijJjKkkraLlLlLlLlLlNnNnNnNnengOoOoOoOEoeRrRrRrSsSsSsSsTtTtTtUuUuUuUuUuUuWwYyYZzZzZzs
SELECT dbo.fn_toEnglish(N'TrikātaĀ')
--TrikataA
虽然解决了 还是谢谢你 辛苦你了 我自己用程序转换csv后再导入数据库实现的
吉普赛的歌 2018-10-29
  • 打赏
  • 举报
 回复
你贴出表结构， 测试数据的脚本， 及想要的结果， 便于人家理解吧
sunshinebean 2018-10-29
  • 打赏
  • 举报
 回复
引用 1 楼 yenange 的回复:
将存储字母的所有字段都设置为 nvarchar 再试试
字段本身就是nvarchar类型的
ダ雨夹雪リ 2018-10-29
  • 打赏
  • 举报
 回复 


--不知道是不是你要的

--1.建表

CREATE TABLE word

(

	[key] NVARCHAR(10),

	[code] INT,

	[value] VARCHAR(10)

)



--2.插入数据 

insert into word values(N'Ā',256,'A')

insert into word values(N'ā',257,'a')

insert into word values(N'Ă',258,'A')

insert into word values(N'ă',259,'a')

insert into word values(N'Ą',260,'A')

insert into word values(N'ą',261,'a')

insert into word values(N'Ć',262,'C')

insert into word values(N'ć',263,'c')

insert into word values(N'Ĉ',264,'C')

insert into word values(N'ĉ',265,'c')

insert into word values(N'Ċ',266,'C')

insert into word values(N'ċ',267,'c')

insert into word values(N'Č',268,'C')

insert into word values(N'č',269,'c')

insert into word values(N'Ď',270,'D')

insert into word values(N'ď',271,'d')

insert into word values(N'Đ',272,'D')

insert into word values(N'đ',273,'d')

insert into word values(N'Ē',274,'E')

insert into word values(N'ē',275,'e')

insert into word values(N'Ĕ',276,'E')

insert into word values(N'ĕ',277,'e')

insert into word values(N'Ė',278,'E')

insert into word values(N'ė',279,'e')

insert into word values(N'Ę',280,'E')

insert into word values(N'ę',281,'e')

insert into word values(N'Ě',282,'E')

insert into word values(N'ě',283,'e')

insert into word values(N'Ĝ',284,'G')

insert into word values(N'ĝ',285,'g')

insert into word values(N'Ğ',286,'G')

insert into word values(N'ğ',287,'g')

insert into word values(N'Ġ',288,'G')

insert into word values(N'ġ',289,'g')

insert into word values(N'Ģ',290,'G')

insert into word values(N'ģ',291,'g')

insert into word values(N'Ĥ',292,'H')

insert into word values(N'ĥ',293,'h')

insert into word values(N'Ħ',294,'H')

insert into word values(N'ħ',295,'h')

insert into word values(N'Ĩ',296,'I')

insert into word values(N'ĩ',297,'i')

insert into word values(N'Ī',298,'I')

insert into word values(N'ī',299,'i')

insert into word values(N'Ĭ',300,'I')

insert into word values(N'ĭ',301,'i')

insert into word values(N'Į',302,'I')

insert into word values(N'į',303,'i')

insert into word values(N'İ',304,'I')

insert into word values(N'ı',305,'i')

insert into word values(N'IJ',306,'IJ')

insert into word values(N'ij',307,'ij')

insert into word values(N'Ĵ',308,'J')

insert into word values(N'ĵ',309,'j')

insert into word values(N'Ķ',310,'K')

insert into word values(N'ķ',311,'k')

insert into word values(N'ĸ',312,'kra')

insert into word values(N'Ĺ',313,'L')

insert into word values(N'ĺ',314,'l')

insert into word values(N'Ļ',315,'L')

insert into word values(N'ļ',316,'l')

insert into word values(N'Ľ',317,'L')

insert into word values(N'ľ',318,'l')

insert into word values(N'Ŀ',319,'L')

insert into word values(N'ŀ',320,'l')

insert into word values(N'Ł',321,'L')

insert into word values(N'ł',322,'l')

insert into word values(N'Ń',323,'N')

insert into word values(N'ń',324,'n')

insert into word values(N'Ņ',325,'N')

insert into word values(N'ņ',326,'n')

insert into word values(N'Ň',327,'N')

insert into word values(N'ň',328,'n')

insert into word values(N'ʼn',329,'N')

insert into word values(N'Ŋ',330,'n')

insert into word values(N'ŋ',331,'eng')

insert into word values(N'Ō',332,'O')

insert into word values(N'ō',333,'o')

insert into word values(N'Ŏ',334,'O')

insert into word values(N'ŏ',335,'o')

insert into word values(N'Ő',336,'O')

insert into word values(N'ő',337,'o')

insert into word values(N'Œ',338,'OE')

insert into word values(N'œ',339,'oe')

insert into word values(N'Ŕ',340,'R')

insert into word values(N'ŕ',341,'r')

insert into word values(N'Ŗ',342,'R')

insert into word values(N'ŗ',343,'r')

insert into word values(N'Ř',344,'R')

insert into word values(N'ř',345,'r')

insert into word values(N'Ś',346,'S')

insert into word values(N'ś',347,'s')

insert into word values(N'Ŝ',348,'S')

insert into word values(N'ŝ',349,'s')

insert into word values(N'Ş',350,'S')

insert into word values(N'ş',351,'s')

insert into word values(N'Š',352,'S')

insert into word values(N'š',353,'s')

insert into word values(N'Ţ',354,'T')

insert into word values(N'ţ',355,'t')

insert into word values(N'Ť',356,'T')

insert into word values(N'ť',357,'t')

insert into word values(N'Ŧ',358,'T')

insert into word values(N'ŧ',359,'t')

insert into word values(N'Ũ',360,'U')

insert into word values(N'ũ',361,'u')

insert into word values(N'Ū',362,'U')

insert into word values(N'ū',363,'u')

insert into word values(N'Ŭ',364,'U')

insert into word values(N'ŭ',365,'u')

insert into word values(N'Ů',366,'U')

insert into word values(N'ů',367,'u')

insert into word values(N'Ű',368,'U')

insert into word values(N'ű',369,'u')

insert into word values(N'Ų',370,'U')

insert into word values(N'ų',371,'u')

insert into word values(N'Ŵ',372,'W')

insert into word values(N'ŵ',373,'w')

insert into word values(N'Ŷ',374,'Y')

insert into word values(N'ŷ',375,'y')

insert into word values(N'Ÿ',376,'Y')

insert into word values(N'Ź',377,'Z')

insert into word values(N'ź',378,'z')

insert into word values(N'Ż',379,'Z')

insert into word values(N'ż',380,'z')

insert into word values(N'Ž',381,'Z')

insert into word values(N'ž',382,'z')

insert into word values(N'ſ',383,'s')



--3.创建函数

CREATE FUNCTION fn_toEnglish(@Val nvarchar(4000))

RETURNS nvarchar(4000)

AS

BEGIN

	DECLARE @Rst NVARCHAR(4000)=''

	DECLARE @I INT=1

	DECLARE @cnt INT= LEN(@Val)

	DECLARE @Tmp NVARCHAR(1)



	WHILE @I<=@cnt

	BEGIN

		SET @Tmp=SUBSTRING(@Val,@I,1)

		IF UNICODE(@Tmp) BETWEEN 256 AND 383

			 SELECT @Rst=@Rst+value FROM word WHERE code= UNICODE(@Tmp)

		ELSE 

		  SET @Rst=@Rst+@Tmp

		SET @I=@I+1

	END  



	RETURN @Rst

END  



--4.测试

SELECT dbo.fn_toEnglish(N'ĀāĂ㥹ĆćĈĉĊċČčĎďĐđĒēĔĕĖėĘęĚěĜĝĞğĠġĢģĤĥĦħĨĩĪīĬĭĮįİıIJijĴĵĶķĸĹĺĻļĽľĿŀŁłŃńŅņŇňʼnŊŋŌōŎŏŐőŒœŔŕŖŗŘřŚśŜŝŞşŠšŢţŤťŦŧŨũŪūŬŭŮůŰűŲųŴŵŶŷŸŹźŻżŽžſ')

--AaAaAaCcCcCcCcDdDdEeEeEeEeEeGgGgGgGgHhHhIiIiIiIiIiIJijJjKkkraLlLlLlLlLlNnNnNnNnengOoOoOoOEoeRrRrRrSsSsSsSsTtTtTtUuUuUuUuUuUuWwYyYZzZzZzs

SELECT dbo.fn_toEnglish(N'TrikātaĀ')

--TrikataA
吉普赛的歌 2018-10-28
  • 打赏
  • 举报
 回复
将存储字母的所有字段都设置为 nvarchar 再试试
MSSQL Server 2008 R2数据库基础知识
本课程详细介绍SQLSERVER数据库的基础管理知识，所使用到的服务及网络协议，实例的概念，应用的文件结构。掌握如何创建数据库，用户与角色的使用，日常管理中经常使用到的维护计划的管理，了解性能优化时应用的优化...
[MSSQL]SQL简繁转换函数
  --全部的简繁对照 declare @jall nvarchar(4000),@fall nvarchar(4000) select @jall=N'啊阿埃挨哎唉哀皑癌蔼矮艾碍爱隘鞍氨安俺按暗岸胺案肮昂盎凹敖熬翱袄傲奥懊澳芭捌扒叭吧笆八疤巴拔跋靶把耙坝霸罢爸白柏百摆佰败拜稗斑班搬扳般颁板版扮拌伴瓣半办绊邦帮梆榜膀绑棒磅蚌镑傍谤苞胞包褒剥薄雹保堡饱宝抱报暴豹鲍爆杯碑悲卑北辈背贝钡倍狈备惫焙被...
SQL中如何将月份转换为英文缩写
原文出处：http://biancheng.dnbcw.info/mssql/109657.html 问：    比如2010-5-31 转换为 31-may-2010. 答： declare @t datetime set @t='2010-5-31' SET LANGUAGE us_english select replace(convert(varchar(11),@t,113
异常处理·MSSQL·在将nvarchar值‘XXX‘转换成数据类型int时失败
阅文时长 | 0.16分钟 字数统计 | 260.8字符 主要内容 | 1、引言&背景 2、声明与参考资料 『异常处理·MSSQL·在将nvarchar值'XXX'转换成数据类型int时失败』 编写人 | SCscHero 编写时间 | 2021/7/17 PM2:31 文章类型 | 系列 完成度 | 已完成 座右铭 每一个伟大的事业，都有一个微不足道的开始。 一、引言&背景   完成度：10..
MSSQL 中将数字转换为字符串
STR 由数字数据转换来的字符数据。 语法 STR ( float_expression [ , length [ , decimal ] ] ) 参数 float_expression 是带小数点的近似数字 (float) 数据类型的表达式。 length 是总长度，包括小数点、符号、数字或空格。默认值为 10。 decimal 是小数点右边的位数。 返回类型 cha
疑难问题

22,210

社区成员

121,727

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章