22,297
社区成员
发帖
与我相关
我的任务
分享请教mssql 拉丁字母怎么转换成正常英文字母?
GEOIP库里有很多拉丁字母,类似截图里这样的.(截图里只是搜了ā相关的)。
所有的拉丁字母可以在这里看到:http://www.runoob.com/charsets/ref-utf-latin-extended-a.html
有没有什么办法可以让select出来的结果是正常的英文字母?比如Trikāta->Trikata
我有尝试过根据拉丁字母表建一个表,一个个去替换...但是mssql好像不能识别拉丁字母的大小写..我用collate Chinese_PRC_CS_AS测试正常的英文字母是可以的,但是测试拉丁字母搜的明明是Ā,但是出来的结果都是ā
...全文
请发表友善的回复…
发表回复
sunshinebean 2018-11-08
- 打赏
- 举报
虽然解决了 还是谢谢你 辛苦你了 我自己用程序转换csv后再导入数据库实现的
吉普赛的歌 2018-10-29
- 打赏
- 举报
你贴出表结构， 测试数据的脚本， 及想要的结果， 便于人家理解吧
sunshinebean 2018-10-29
- 打赏
- 举报
字段本身就是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 再试试
