Convert VBA function to SQL function
-
I have VBA function need to convert to SQL function.
Kindly help me change it.
Here is a snippet of the code.
Public Function VniToUni(str$) As String
Dim VNI$, UNI$, i&, sUni$, arrUNI() As String
VNI = "aù,aø,aû,aõ,aï,aâ,aê,aá,aà,aå,aã,aä,aé,aè,aú,aü,aë,AÙ,AØ,AÛ,AÕ,AÏ,AÂ,AÊ,AÁ,AÀ,AÅ,AÃ,AÄ,AÉ,AÈ,AÚ,AÜ,AË,eù,eø,eû,eõ,eï,eâ,eá,eà,eå,eã,eä,EÙ,EØ,EÛ,EÕ,EÏ,EÂ,EÁ,EÀ,EÅ,EÃ,EÄ,í ,ì ,æ ,ó ,ò ,Í ,Ì ,Æ ,Ó ,Ò ,où,oø,oû,oõ,oï,oâ,ô,oá,oà,oå,oã,oä,ôù,ôø,ôû,ôõ,ôï,OÙ,OØ,OÛ,OÕ,OÏ,OÂ,Ô ,OÁ,OÀ,OÅ,OÃ,OÄ,ÔÙ,ÔØ,ÔÛ,ÔÕ,ÔÏ,uù,uø,uû,uõ,uï,ö ,öù,öø,öû,öõ,öï,UÙ,UØ,UÛ,UÕ,UÏ,Ö ,ÖÙ,ÖØ,ÖÛ,ÖÕ,ÖÏ,yù,yø,yû,yõ,î ,YÙ,YØ,YÛ,YÕ,Î ,ñ ,Ñ "
UNI = "E1,E0,1EA3,E3,1EA1,E2,103,1EA5,1EA7,1EA9,1EAB,1EAD,1EAF,1EB1,1EB3,1EB5,1EB7,C1,C0,1EA2,C3,1EA0,C2,102,1EA4,1EA6,1EA8,1EAA,1EAC,1EAE,1EB0,1EB2,1EB4,1EB6,E9,E8,1EBB,1EBD,1EB9,EA,1EBF,1EC1,1EC3,1EC5,1EC7,C9,C8,1EBA,1EBC,1EB8,CA,1EBE,1EC0,1EC2,1EC4,1EC6,ED,EC,1EC9,129,1ECB,CD,CC,1EC8,128,1ECA,F3,F2,1ECF,F5,1ECD,F4,1A1,1ED1,1ED3,1ED5,1ED7,1ED9,1EDB,1EDD,1EDF,1EE1,1EE3,D3,D2,1ECE,D5,1ECC,D4,1A0,1ED0,1ED2,1ED4,1ED6,1ED8,1EDA,1EDC,1EDE,1EE0,1EE2,FA,F9,1EE7,169,1EE5,1B0,1EE9,1EEB,1EED,1EEF,1EF1,DA,D9,1EE6,168,1EE4,1AF,1EE8,1EEA,1EEC,1EEE,1EF0,FD,1EF3,1EF7,1EF9,1EF5,DD,1EF2,1EF6,1EF8,1EF4,111,110"
arrUNI = Split(UNI, ",")
For i = 1 To Len(str)
If InStr(VNI, Mid(str, i, 2)) > 0 And Len(Mid(str, i, 2)) = 2 Then
sUni = sUni & ChrW("&h" & arrUNI(InStr(VNI, Mid(str, i, 2)) \ 3))
i = i + 1
ElseIf InStr(VNI, Mid(str, i, 1) & " ") > 0 Then
sUni = sUni & ChrW("&h" & arrUNI(InStr(VNI, Mid(str, i, 1) & " ") \ 3))
End If
If InStr(VNI, Mid(str, i, 1)) = 0 Or InStr("a,A,e,E,o,O,u,U,y,Y, ", Mid(str, i, 1)) > 0 Then sUni = sUni & Mid(str, i, 1)
Next
VniToUni = sUni
End Function
-
Please tell us what this function does, in words.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
If I understand that script correctly, you are essentially doing a find and replace with a large list of characters to swap out, correct? If the character is "aù", you are replacing it with &hE1.
If that is correct, my suggestion is don't do this in SQL. If you MUST do this in SQL, I'd do it with a LOT of REPLACE statements. It'll likely look quite messy though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
You might be able to use UNICODE to convert the VNI characters to their equivalent unicode values:
Select unicode(N'à'), nchar(unicode(N'à'))
If that is what you are looking for - then you should be able to build an inline-table valued function using a tally/numbers table to loop over every character and convert to the equivalent unicode values.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
The REPLACE function can do many things in SQL Server. It's not really documented well and I usually have to experiment with it. When the REPLACE function is itself called in a function then it can do multiple substitutions at once. It doesn't work outside of a scalar function afaik. Suppose the OP creates a table and uses a string splitter (with row number to join on) to populate it. If they create a scalar function which references that look up, then the REPLACE function will perform all of the replacements from the lookup table. It works hierarchically by length of search term, I'm pretty sure.
drop function if exists dbo.json_rn_splitter;
go
create function dbo.json_rn_splitter(
@stringnvarchar(max),
@delimiter nvarchar(3))
returns table as
return
with
json_cte(nested_json) as(
select concat(N'{ "nested_json" : [{ "split_string":"', replace(@string, @delimiter, N'"},{"split_string":"'), N'"}]}'))
select
j.split_string,
row_number() over (order by jc.nested_json) row_num
from
json_cte jc
cross apply
openjson(jc.nested_json, N'strict $.nested_json') with (split_string nvarchar(max)) j;
go
drop table if exists dbo.test_vni_uni;
go
create table dbo.test_vni_uni(
vni nvarchar(5),
uni nvarchar(5),
constraint
unq_vni_uni unique(vni, uni));
drop function if exists dbo.test_uni_vni_replace;
go
create function dbo.test_uni_vni_replace(
@stringnvarchar(max))
returns nvarchar(max) as
begin
select
@string = replace(@string collate Latin1_General_CS_AS, vni, uni)
from
dbo.test_vni_uni;
return @string;
end
go
declare
@vni nvarchar(max)='aù,aø,aû,aõ,aï,aâ,aê,aá,aà,aå,aã,aä,aé,aè,aú,aü,aë,AÙ,AØ,AÛ,AÕ,AÏ,AÂ,AÊ,AÁ,AÀ,AÅ,AÃ,AÄ,AÉ,AÈ,AÚ,AÜ,AË,eù,eø,eû,eõ,eï,eâ,eá,eà,eå,eã,eä,EÙ,EØ,EÛ,EÕ,EÏ,EÂ,EÁ,EÀ,EÅ,EÃ,EÄ,í ,ì ,æ ,ó ,ò ,Í ,Ì ,Æ ,Ó ,Ò ,où,oø,oû,oõ,oï,oâ,ô,oá,oà,oå,oã,oä,ôù,ôø,ôû,ôõ,ôï,OÙ,OØ,OÛ,OÕ,OÏ,OÂ,Ô ,OÁ,OÀ,OÅ,OÃ,OÄ,ÔÙ,ÔØ,ÔÛ,ÔÕ,ÔÏ,uù,uø,uû,uõ,uï,ö ,öù,öø,öû,öõ,öï,UÙ,UØ,UÛ,UÕ,UÏ,Ö ,ÖÙ,ÖØ,ÖÛ,ÖÕ,ÖÏ,yù,yø,yû,yõ,î ,YÙ,YØ,YÛ,YÕ,Î ,ñ ,Ñ ',
@uni nvarchar(max)='E1,E0,1EA3,E3,1EA1,E2,103,1EA5,1EA7,1EA9,1EAB,1EAD,1EAF,1EB1,1EB3,1EB5,1EB7,C1,C0,1EA2,C3,1EA0,C2,102,1EA4,1EA6,1EA8,1EAA,1EAC,1EAE,1EB0,1EB2,1EB4,1EB6,E9,E8,1EBB,1EBD,1EB9,EA,1EBF,1EC1,1EC3,1EC5,1EC7,C9,C8,1EBA,1EBC,1EB8,CA,1EBE,1EC0,1EC2,1EC4,1EC6,ED,EC,1EC9,129,1ECB,CD,CC,1EC8,128,1ECA,F3,F2,1ECF,F5,1ECD,F4,1A1,1ED1,1ED3,1ED5,1ED7,1ED9,1EDB,1EDD,1EDF,1EE1,1EE3,D3,D2,1ECE,D5,1ECC,D4,1A0,1ED0,1ED2,1ED4,1ED6,1ED8,1EDA,1EDC,1EDE,1EE0,1EE2,FA,F9,1EE7,169,1EE5,1B0,1EE9,1EEB,1EED,1EEF,1EF1,DA,D9,1EE6,168,1EE4,1AF,1EE8,1EEA,1EEC,1EEE,1EF0,FD,1EF3,1EF7,1EF9,1EF5,DD,1EF2,1EF6,1EF8,1EF4,111,110';
insert dbo.test_vni_uni(vni, uni)
select
v.split_string, u.split_string
from
dbo.json_rn_splitter(@uni, N',') u
join
dbo.json_rn_splitter(@vni, N',') v on u.row_num=v.row_num;
declare
@str nvarchar(max)='EÙtEØxxEÛttEÕssEÏeeEÂhhEÁ';
declare
@fixed_str nvarchar(max)=dbo.test_uni_vni_replace(@str);
select @fixed_str;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Dear scdecade,
Your function can not use in my database, my database running on SQL server 2014 and can not support JSON.
Pls help me fix it.
Thank you.
-
Dear Phil Parkin,
I'm Vietnamese. In Vietnamese language using 2 tpye: unicode encoding and VNI encoding. This function used for convert from Unicode to VNI.
Example: Unicode: "Lê Minh Trung" ->> VNI: "Leâ Minh Trung"
Tks.
- trunglm wrote:
Dear scdecade,
Your function can not use in my database, my database running on SQL server 2014 and can not support JSON.
Pls help me fix it.
Thank you.
According to this article, https://stackoverflow.com/questions/31236730/convert-the-vni-windows-into-utf-8, there isn't a built in conversion. The only json part of the script is the splitter which is probably 10x slower then Jeff Moden's tally-based way anyway. At the end of this article in the resources links to new splitters there's a 4k (unicocde) splitter:
Other than that the script should work. I'm not sure about the spaces in the input strings, maybe those aren't necessary?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
I pulled the list of viet character encodings from http://vietunicode.sourceforge.net/charset/
Using that list to build the table you can then use that table to convert to/from multiple character encoding. The function is a scalar function and may not perform as well as needed - if that is the case you need to look at how to make the function an inline-table valued function.
The key to an inline-table valued function will be a tally table that 'loops' over each character in the string and performs the replace character by character.
Drop Table If Exists dbo.viet_character_encoding;
Go
Create Table dbo.viet_character_encoding (
viet nchar(1)
, unicode_hex binary(2)
, vni nchar(2)
, vps nchar(3)
, viscii nchar(3)
, tcvn3 nchar(3)
, viqr nchar(4)
, english_name nvarchar(100)
);
Go
Insert Into dbo.viet_character_encoding (viet, unicode_hex, vni, vps, viscii, tcvn3, viqr, english_name)
Values (N'À', cast(reverse(0x00C0) As varbinary), N'AØ', N'€', N'À', N'Aµ', N'A`', N'LATIN CAPITAL LETTER A WITH GRAVE')
, (N'Á', cast(reverse(0x00C1) As varbinary), N'AÙ', N'Á', N'Á', N'A¸', N'A''', N'LATIN CAPITAL LETTER A WITH ACUTE')
, (N'Â', cast(reverse(0x00C2) As varbinary), N'AÂ', N'Â', N'Â', N'¢', N'A^', N'LATIN CAPITAL LETTER A WITH CIRCUMFLEX')
, (N'Ã', cast(reverse(0x00C3) As varbinary), N'AÕ', N'‚', N'Ã', N'A·', N'A~', N'LATIN CAPITAL LETTER A WITH TILDE')
, (N'È', cast(reverse(0x00C8) As varbinary), N'EØ', N'×', N'È', N'EÌ', N'E`', N'LATIN CAPITAL LETTER E WITH GRAVE')
, (N'É', cast(reverse(0x00C9) As varbinary), N'EÙ', N'É', N'É', N'EÐ', N'E''', N'LATIN CAPITAL LETTER E WITH ACUTE')
, (N'Ê', cast(reverse(0x00CA) As varbinary), N'EÂ', N'Ê', N'Ê', N'£', N'E^', N'LATIN CAPITAL LETTER E WITH CIRCUMFLEX')
, (N'Ì', cast(reverse(0x00CC) As varbinary), N'Ì', N'µ', N'Ì', N'I×', N'I`', N'LATIN CAPITAL LETTER I WITH GRAVE')
, (N'Í', cast(reverse(0x00CD) As varbinary), N'Í', N'´', N'Í', N'IÝ', N'I''', N'LATIN CAPITAL LETTER I WITH ACUTE')
, (N'Ò', cast(reverse(0x00D2) As varbinary), N'OØ', N'¼', N'Ò', N'Oß', N'O`', N'LATIN CAPITAL LETTER O WITH GRAVE')
, (N'Ó', cast(reverse(0x00D3) As varbinary), N'OÙ', N'¹', N'Ó', N'Oã', N'O''', N'LATIN CAPITAL LETTER O WITH ACUTE')
, (N'Ô', cast(reverse(0x00D4) As varbinary), N'OÂ', N'Ô', N'Ô', N'¤', N'O^', N'LATIN CAPITAL LETTER O WITH CIRCUMFLEX')
, (N'Õ', cast(reverse(0x00D5) As varbinary), N'OÕ', N'¾', N'', N'Oâ', N'O~', N'LATIN CAPITAL LETTER O WITH TILDE')
, (N'Ù', cast(reverse(0x00D9) As varbinary), N'UØ', N'¨', N'Ù', N'Uï', N'U`', N'LATIN CAPITAL LETTER U WITH GRAVE')
, (N'Ú', cast(reverse(0x00DA) As varbinary), N'UÙ', N'Ú', N'Ú', N'Uó', N'U''', N'LATIN CAPITAL LETTER U WITH ACUTE')
, (N'Ý', cast(reverse(0x00DD) As varbinary), N'YÙ', N'Ý', N'Ý', N'Yý', N'Y''', N'LATIN CAPITAL LETTER Y WITH ACUTE')
, (N'à', cast(reverse(0x00E0) As varbinary), N'aø', N'à', N'à', N'µ', N'a`', N'LATIN SMALL LETTER A WITH GRAVE')
, (N'á', cast(reverse(0x00E1) As varbinary), N'aù', N'á', N'á', N'¸', N'a''', N'LATIN SMALL LETTER A WITH ACUTE')
, (N'â', cast(reverse(0x00E2) As varbinary), N'aâ', N'â', N'â', N'©', N'a^', N'LATIN SMALL LETTER A WITH CIRCUMFLEX')
, (N'ã', cast(reverse(0x00E3) As varbinary), N'aõ', N'ã', N'ã', N'·', N'a~', N'LATIN SMALL LETTER A WITH TILDE')
, (N'è', cast(reverse(0x00E8) As varbinary), N'eø', N'è', N'è', N'Ì', N'e`', N'LATIN SMALL LETTER E WITH GRAVE')
, (N'é', cast(reverse(0x00E9) As varbinary), N'eù', N'é', N'é', N'Ð', N'e''', N'LATIN SMALL LETTER E WITH ACUTE')
, (N'ê', cast(reverse(0x00EA) As varbinary), N'eâ', N'ê', N'ê', N'ª', N'e^', N'LATIN SMALL LETTER E WITH CIRCUMFLEX')
, (N'ì', cast(reverse(0x00EC) As varbinary), N'ì', N'ì', N'ì', N'×', N'i`', N'LATIN SMALL LETTER I WITH GRAVE')
, (N'í', cast(reverse(0x00ED) As varbinary), N'í', N'í', N'í', N'Ý', N'i''', N'LATIN SMALL LETTER I WITH ACUTE')
, (N'ò', cast(reverse(0x00F2) As varbinary), N'oø', N'ò', N'ò', N'ß', N'o`', N'LATIN SMALL LETTER O WITH GRAVE')
, (N'ó', cast(reverse(0x00F3) As varbinary), N'où', N'ó', N'ó', N'ã', N'o''', N'LATIN SMALL LETTER O WITH ACUTE')
, (N'ô', cast(reverse(0x00F4) As varbinary), N'oâ', N'ô', N'ô', N'«', N'o^', N'LATIN SMALL LETTER O WITH CIRCUMFLEX')
, (N'õ', cast(reverse(0x00F5) As varbinary), N'oõ', N'õ', N'õ', N'â', N'o~', N'LATIN SMALL LETTER O WITH TILDE')
, (N'ù', cast(reverse(0x00F9) As varbinary), N'uø', N'ù', N'ù', N'ï', N'u`', N'LATIN SMALL LETTER U WITH GRAVE')
, (N'ú', cast(reverse(0x00FA) As varbinary), N'uù', N'ú', N'ú', N'ó', N'u''', N'LATIN SMALL LETTER U WITH ACUTE')
, (N'ý', cast(reverse(0x00FD) As varbinary), N'yù', N'š', N'ý', N'ý', N'y''', N'LATIN SMALL LETTER Y WITH ACUTE')
, (N'A', cast(reverse(0x0102) As varbinary), N'AÊ', N'ˆ', N'Å', N'¡', N'A(N', N'LATIN CAPITAL LETTER A WITH BREVE')
, (N'a', cast(reverse(0x0103) As varbinary), N'aê', N'æ', N'å', N'¨', N'a(N', N'LATIN SMALL LETTER A WITH BREVE')
, (N'Ð', cast(reverse(0x0110) As varbinary), N'Ñ', N'ñ', N'Ð', N'§', N'DD', N'LATIN CAPITAL LETTER D WITH STROKE')
, (N'd', cast(reverse(0x0111) As varbinary), N'ñ', N'Ç', N'ð', N'®', N'dd', N'LATIN SMALL LETTER D WITH STROKE')
, (N'I', cast(reverse(0x0128) As varbinary), N'Ó', N'¸', N'Î', N'IÜ', N'I~', N'LATIN CAPITAL LETTER I WITH TILDE')
, (N'i', cast(reverse(0x0129) As varbinary), N'ó', N'ï', N'î', N'Ü', N'i~', N'LATIN SMALL LETTER I WITH TILDE')
, (N'U', cast(reverse(0x0168) As varbinary), N'UÕ', N'¬', N'', N'Uò', N'U~', N'LATIN CAPITAL LETTER U WITH TILDE')
, (N'u', cast(reverse(0x0169) As varbinary), N'uõ', N'Û', N'û', N'ò', N'u~', N'LATIN SMALL LETTER U WITH TILDE')
, (N'O', cast(reverse(0x01A0) As varbinary), N'Ô', N'÷', N'´', N'¥', N'O+', N'LATIN CAPITAL LETTER O WITH HORN')
, (N'o', cast(reverse(0x01A1) As varbinary), N'ô', N'Ö', N'½', N'¬', N'o+', N'LATIN SMALL LETTER O WITH HORN')
, (N'U', cast(reverse(0x01AF) As varbinary), N'Ö', N'Ð', N'¿', N'¦', N'U+', N'LATIN CAPITAL LETTER U WITH HORN')
, (N'u', cast(reverse(0x01B0) As varbinary), N'ö', N'Ü', N'ß', N'', N'u+', N'LATIN SMALL LETTER U WITH HORN')
, (N'?', cast(reverse(0x1EA0) As varbinary), N'AÏ', N'[1]', N'€', N'A¹', N'A.', N'LATIN CAPITAL LETTER A WITH DOT BELOW')
, (N'?', cast(reverse(0x1EA1) As varbinary), N'aï', N'å', N'Õ', N'¹', N'a.', N'LATIN SMALL LETTER A WITH DOT BELOW')
, (N'?', cast(reverse(0x1EA2) As varbinary), N'AÛ', N'', N'Ä', N'A¶', N'A?', N'LATIN CAPITAL LETTER A WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EA3) As varbinary), N'aû', N'ä', N'ä', N'¶', N'a?', N'LATIN SMALL LETTER A WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EA4) As varbinary), N'AÁ', N'ƒ', N'„', N'¢Ê', N'A^''', N'LATIN CAPITAL LETTER A WITH CIRCUMFLEX AND ACUTE')
, (N'?', cast(reverse(0x1EA5) As varbinary), N'aá', N'Ã', N'¤', N'Ê', N'a^''', N'LATIN SMALL LETTER A WITH CIRCUMFLEX AND ACUTE')
, (N'?', cast(reverse(0x1EA6) As varbinary), N'AÀ', N'„', N'…', N'¢Ç', N'A^`', N'LATIN CAPITAL LETTER A WITH CIRCUMFLEX AND GRAVE')
, (N'?', cast(reverse(0x1EA7) As varbinary), N'aà', N'À', N'¥', N'Ç', N'a^`', N'LATIN SMALL LETTER A WITH CIRCUMFLEX AND GRAVE')
, (N'?', cast(reverse(0x1EA8) As varbinary), N'AÅ', N'…', N'†', N'¢È', N'A^?', N'LATIN CAPITAL LETTER A WITH CIRCUMFLEX AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EA9) As varbinary), N'aå', N'Ä', N'¦', N'È', N'a^?', N'LATIN SMALL LETTER A WITH CIRCUMFLEX AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EAA) As varbinary), N'AÃ', N'', N'', N'¢É', N'A^~', N'LATIN CAPITAL LETTER A WITH CIRCUMFLEX AND TILDE')
, (N'?', cast(reverse(0x1EAB) As varbinary), N'aã', N'Å', N'ç', N'É', N'a^~', N'LATIN SMALL LETTER A WITH CIRCUMFLEX AND TILDE')
, (N'?', cast(reverse(0x1EAC) As varbinary), N'AÄ', N'', N'‡', N'¢Ë', N'A^.', N'LATIN CAPITAL LETTER A WITH CIRCUMFLEX AND DOT BELOW')
, (N'?', cast(reverse(0x1EAD) As varbinary), N'aä', N'Æ', N'§', N'Ë', N'a^.', N'LATIN SMALL LETTER A WITH CIRCUMFLEX AND DOT BELOW')
, (N'?', cast(reverse(0x1EAE) As varbinary), N'AÉ', N'', N'', N'¡¾', N'A(N''', N'LATIN CAPITAL LETTER A WITH BREVE AND ACUTE')
, (N'?', cast(reverse(0x1EAF) As varbinary), N'aé', N'¡', N'¡', N'¾', N'a(N''', N'LATIN SMALL LETTER A WITH BREVE AND ACUTE')
, (N'?', cast(reverse(0x1EB0) As varbinary), N'AÈ', N'Ž', N'‚', N'¡»', N'A(`', N'LATIN CAPITAL LETTER A WITH BREVE AND GRAVE')
, (N'?', cast(reverse(0x1EB1) As varbinary), N'aè', N'¢', N'¢', N'»', N'a(`', N'LATIN SMALL LETTER A WITH BREVE AND GRAVE')
, (N'?', cast(reverse(0x1EB2) As varbinary), N'AÚ', N'', N'[1]', N'¡¼', N'A(?', N'LATIN CAPITAL LETTER A WITH BREVE AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EB3) As varbinary), N'aú', N'£', N'Æ', N'¼', N'a(?', N'LATIN SMALL LETTER A WITH BREVE AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EB4) As varbinary), N'AÜ', N'ð', N'', N'¡½', N'A(~', N'LATIN CAPITAL LETTER A WITH BREVE AND TILDE')
, (N'?', cast(reverse(0x1EB5) As varbinary), N'aü', N'¤', N'Ç', N'½', N'a(~', N'LATIN SMALL LETTER A WITH BREVE AND TILDE')
, (N'?', cast(reverse(0x1EB6) As varbinary), N'AË', N'', N'ƒ', N'¡Æ', N'A(.', N'LATIN CAPITAL LETTER A WITH BREVE AND DOT BELOW')
, (N'?', cast(reverse(0x1EB7) As varbinary), N'aë', N'¥', N'£', N'Æ', N'a(.', N'LATIN SMALL LETTER A WITH BREVE AND DOT BELOW')
, (N'?', cast(reverse(0x1EB8) As varbinary), N'EÏ', N'', N'‰', N'EÑ', N'E.', N'LATIN CAPITAL LETTER E WITH DOT BELOW')
, (N'?', cast(reverse(0x1EB9) As varbinary), N'eï', N'Ë', N'©', N'Ñ', N'e.', N'LATIN SMALL LETTER E WITH DOT BELOW')
, (N'?', cast(reverse(0x1EBA) As varbinary), N'EÛ', N'Þ', N'Ë', N'EÎ', N'E?', N'LATIN CAPITAL LETTER E WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EBB) As varbinary), N'eû', N'È', N'ë', N'Î', N'e?', N'LATIN SMALL LETTER E WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EBC) As varbinary), N'EÕ', N'þ', N'ˆ', N'EÏ', N'E~', N'LATIN CAPITAL LETTER E WITH TILDE')
, (N'?', cast(reverse(0x1EBD) As varbinary), N'eõ', N'ë', N'¨', N'Ï', N'e~', N'LATIN SMALL LETTER E WITH TILDE')
, (N'?', cast(reverse(0x1EBE) As varbinary), N'EÁ', N'', N'Š', N'£Õ', N'E^''', N'LATIN CAPITAL LETTER E WITH CIRCUMFLEX AND ACUTE')
, (N'?', cast(reverse(0x1EBF) As varbinary), N'eá', N'‰', N'ª', N'Õ', N'e^''', N'LATIN SMALL LETTER E WITH CIRCUMFLEX AND ACUTE')
, (N'?', cast(reverse(0x1EC0) As varbinary), N'EÀ', N'“', N'‹', N'£Ò', N'E^`', N'LATIN CAPITAL LETTER E WITH CIRCUMFLEX AND GRAVE')
, (N'?', cast(reverse(0x1EC1) As varbinary), N'eà', N'Š', N'«', N'Ò', N'e^`', N'LATIN SMALL LETTER E WITH CIRCUMFLEX AND GRAVE')
, (N'?', cast(reverse(0x1EC2) As varbinary), N'EÅ', N'”', N'Œ', N'£Ó', N'E^?', N'LATIN CAPITAL LETTER E WITH CIRCUMFLEX AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EC3) As varbinary), N'eå', N'‹', N'¬', N'Ó', N'e^?', N'LATIN SMALL LETTER E WITH CIRCUMFLEX AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EC4) As varbinary), N'EÃ', N'•', N'', N'£Ô', N'E^~', N'LATIN CAPITAL LETTER E WITH CIRCUMFLEX AND TILDE')
, (N'?', cast(reverse(0x1EC5) As varbinary), N'eã', N'Í', N'', N'Ô', N'e^~', N'LATIN SMALL LETTER E WITH CIRCUMFLEX AND TILDE')
, (N'?', cast(reverse(0x1EC6) As varbinary), N'EÄ', N'', N'Ž', N'£Ö', N'E^.', N'LATIN CAPITAL LETTER E WITH CIRCUMFLEX AND DOT BELOW')
, (N'?', cast(reverse(0x1EC7) As varbinary), N'eä', N'Œ', N'®', N'Ö', N'e^.', N'LATIN SMALL LETTER E WITH CIRCUMFLEX AND DOT BELOW')
, (N'?', cast(reverse(0x1EC8) As varbinary), N'Æ', N'·', N'›', N'IØ', N'I?', N'LATIN CAPITAL LETTER I WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EC9) As varbinary), N'æ', N'Ì', N'ï', N'Ø', N'i?', N'LATIN SMALL LETTER I WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1ECA) As varbinary), N'Ò', N'', N'˜', N'IÞ', N'I.', N'LATIN CAPITAL LETTER I WITH DOT BELOW')
, (N'?', cast(reverse(0x1ECB) As varbinary), N'ò', N'Î', N'¸', N'Þ', N'i.', N'LATIN SMALL LETTER I WITH DOT BELOW')
, (N'?', cast(reverse(0x1ECC) As varbinary), N'OÏ', N'', N'š', N'Oä', N'O.', N'LATIN CAPITAL LETTER O WITH DOT BELOW')
, (N'?', cast(reverse(0x1ECD) As varbinary), N'oï', N'†', N'÷', N'ä', N'o.', N'LATIN SMALL LETTER O WITH DOT BELOW')
, (N'?', cast(reverse(0x1ECE) As varbinary), N'OÛ', N'½', N'™', N'Oá', N'O?', N'LATIN CAPITAL LETTER O WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1ECF) As varbinary), N'oû', N'Õ', N'ö', N'á', N'o?', N'LATIN SMALL LETTER O WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1ED0) As varbinary), N'OÁ', N'–', N'', N'¤è', N'O^''', N'LATIN CAPITAL LETTER O WITH CIRCUMFLEX AND ACUTE')
, (N'?', cast(reverse(0x1ED1) As varbinary), N'oá', N'Ó', N'¯', N'è', N'o^''', N'LATIN SMALL LETTER O WITH CIRCUMFLEX AND ACUTE')
, (N'?', cast(reverse(0x1ED2) As varbinary), N'OÀ', N'—', N'', N'¤å', N'O^`', N'LATIN CAPITAL LETTER O WITH CIRCUMFLEX AND GRAVE')
, (N'?', cast(reverse(0x1ED3) As varbinary), N'oà', N'Ò', N'°', N'å', N'o^`', N'LATIN SMALL LETTER O WITH CIRCUMFLEX AND GRAVE')
, (N'?', cast(reverse(0x1ED4) As varbinary), N'OÅ', N'˜', N'‘', N'¤æ', N'O^?', N'LATIN CAPITAL LETTER O WITH CIRCUMFLEX AND HOOK ABOVE')
, (N'?', cast(reverse(0x1ED5) As varbinary), N'oå', N'°', N'±', N'æ', N'o^?', N'LATIN SMALL LETTER O WITH CIRCUMFLEX AND HOOK ABOVE')
, (N'?', cast(reverse(0x1ED6) As varbinary), N'OÃ', N'™', N'’', N'¤ç', N'O^~', N'LATIN CAPITAL LETTER O WITH CIRCUMFLEX AND TILDE')
, (N'?', cast(reverse(0x1ED7) As varbinary), N'oã', N'‡', N'²', N'ç', N'o^~', N'LATIN SMALL LETTER O WITH CIRCUMFLEX AND TILDE')
, (N'?', cast(reverse(0x1ED8) As varbinary), N'OÄ', N'', N'“', N'¤é', N'O^.', N'LATIN CAPITAL LETTER O WITH CIRCUMFLEX AND DOT BELOW')
, (N'?', cast(reverse(0x1ED9) As varbinary), N'oä', N'¶', N'µ', N'é', N'o^.', N'LATIN SMALL LETTER O WITH CIRCUMFLEX AND DOT BELOW')
, (N'?', cast(reverse(0x1EDA) As varbinary), N'ÔÙ', N'', N'•', N'¥í', N'O+''', N'LATIN CAPITAL LETTER O WITH HORN AND ACUTE')
, (N'?', cast(reverse(0x1EDB) As varbinary), N'ôù', N'§', N'¾', N'í', N'o+''', N'LATIN SMALL LETTER O WITH HORN AND ACUTE')
, (N'?', cast(reverse(0x1EDC) As varbinary), N'ÔØ', N'ž', N'–', N'¥ê', N'O+`', N'LATIN CAPITAL LETTER O WITH HORN AND GRAVE')
, (N'?', cast(reverse(0x1EDD) As varbinary), N'ôø', N'©', N'¶', N'ê', N'o+`', N'LATIN SMALL LETTER O WITH HORN AND GRAVE')
, (N'?', cast(reverse(0x1EDE) As varbinary), N'ÔÛ', N'Ÿ', N'—', N'¥ë', N'O+?', N'LATIN CAPITAL LETTER O WITH HORN AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EDF) As varbinary), N'ôû', N'ª', N'·', N'ë', N'o+?', N'LATIN SMALL LETTER O WITH HORN AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EE0) As varbinary), N'ÔÕ', N'¦', N'³', N'¥ì', N'O+~', N'LATIN CAPITAL LETTER O WITH HORN AND TILDE')
, (N'?', cast(reverse(0x1EE1) As varbinary), N'ôõ', N'«', N'Þ', N'ì', N'o+~', N'LATIN SMALL LETTER O WITH HORN AND TILDE')
, (N'?', cast(reverse(0x1EE2) As varbinary), N'ÔÏ', N'', N'”', N'¥î', N'O+.', N'LATIN CAPITAL LETTER O WITH HORN AND DOT BELOW')
, (N'?', cast(reverse(0x1EE3) As varbinary), N'ôï', N'®', N'þ', N'î', N'o+.', N'LATIN SMALL LETTER O WITH HORN AND DOT BELOW')
, (N'?', cast(reverse(0x1EE4) As varbinary), N'UÏ', N'', N'ž', N'Uô', N'U.', N'LATIN CAPITAL LETTER U WITH DOT BELOW')
, (N'?', cast(reverse(0x1EE5) As varbinary), N'uï', N'ø', N'ø', N'ô', N'u.', N'LATIN SMALL LETTER U WITH DOT BELOW')
, (N'?', cast(reverse(0x1EE6) As varbinary), N'UÛ', N'Ñ', N'œ', N'Uñ', N'U?', N'LATIN CAPITAL LETTER U WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EE7) As varbinary), N'uû', N'û', N'ü', N'ñ', N'u?', N'LATIN SMALL LETTER U WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EE8) As varbinary), N'ÖÙ', N'', N'º', N'¦ø', N'U+''', N'LATIN CAPITAL LETTER U WITH HORN AND ACUTE')
, (N'?', cast(reverse(0x1EE9) As varbinary), N'öù', N'Ù', N'Ñ', N'ø', N'u+''', N'LATIN SMALL LETTER U WITH HORN AND ACUTE')
, (N'?', cast(reverse(0x1EEA) As varbinary), N'ÖØ', N'¯', N'»', N'¦õ', N'U+`', N'LATIN CAPITAL LETTER U WITH HORN AND GRAVE')
, (N'?', cast(reverse(0x1EEB) As varbinary), N'öø', N'Ø', N'×', N'õ', N'u+`', N'LATIN SMALL LETTER U WITH HORN AND GRAVE')
, (N'?', cast(reverse(0x1EEC) As varbinary), N'ÖÛ', N'±', N'¼', N'¦ö', N'U+?', N'LATIN CAPITAL LETTER U WITH HORN AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EED) As varbinary), N'öû', N'º', N'Ø', N'ö', N'u+?', N'LATIN SMALL LETTER U WITH HORN AND HOOK ABOVE')
, (N'?', cast(reverse(0x1EEE) As varbinary), N'ÖÕ', N'', N'ÿ', N'¦÷', N'U+~', N'LATIN CAPITAL LETTER U WITH HORN AND TILDE')
, (N'?', cast(reverse(0x1EEF) As varbinary), N'öõ', N'»', N'æ', N'÷', N'u+~', N'LATIN SMALL LETTER U WITH HORN AND TILDE')
, (N'?', cast(reverse(0x1EF0) As varbinary), N'ÖÏ', N'', N'¹', N'¦ù', N'U+.', N'LATIN CAPITAL LETTER U WITH HORN AND DOT BELOW')
, (N'?', cast(reverse(0x1EF1) As varbinary), N'öï', N'¿', N'ñ', N'ù', N'u+.', N'LATIN SMALL LETTER U WITH HORN AND DOT BELOW')
, (N'?', cast(reverse(0x1EF2) As varbinary), N'YØ', N'²', N'Ÿ', N'Yú', N'Y`', N'LATIN CAPITAL LETTER Y WITH GRAVE')
, (N'?', cast(reverse(0x1EF3) As varbinary), N'yø', N'ÿ', N'Ï', N'ú', N'y`', N'LATIN SMALL LETTER Y WITH GRAVE')
, (N'?', cast(reverse(0x1EF4) As varbinary), N'Î', N'', N'-', N'Yþ', N'Y.', N'LATIN CAPITAL LETTER Y WITH DOT BELOW')
, (N'?', cast(reverse(0x1EF5) As varbinary), N'î', N'œ', N'Ü', N'þ', N'y.', N'LATIN SMALL LETTER Y WITH DOT BELOW')
, (N'?', cast(reverse(0x1EF6) As varbinary), N'YÛ', N'ý', N'', N'Yû', N'Y?', N'LATIN CAPITAL LETTER Y WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EF7) As varbinary), N'yû', N'›', N'Ö', N'û', N'y?', N'LATIN SMALL LETTER Y WITH HOOK ABOVE')
, (N'?', cast(reverse(0x1EF8) As varbinary), N'YÕ', N'³', N'', N'Yü', N'Y~', N'LATIN CAPITAL LETTER Y WITH TILDE')
, (N'?', cast(reverse(0x1EF9) As varbinary), N'yõ', N'Ï', N'Û', N'ü', N'y~', N'LATIN SMALL LETTER Y WITH TILDE');
Drop Function If Exists dbo.test_uni_vni_replace;
Go
Create Function dbo.test_uni_vni_replace (
@string nvarchar(max)
)
Returns nvarchar(max)
As
Begin
Select @string = replace(@string collate Latin1_General_CS_AS, vni, unicode_hex)
From dbo.viet_character_encoding;
Return @string;
End
Go
Declare @fromString nvarchar(max) = 'Leâ Minh Trung';
Declare @toString nvarchar(max) = dbo.test_uni_vni_replace(@fromString);
Select @fromString, @toString;
Go
Drop Table dbo.viet_character_encoding;
Drop Function dbo.test_uni_vni_replace;By including the different encoding in the table - this one source table can be used in multiple functions for specific conversions.
Edit: @scdecade - I used your function...but instead of building a string to be parsed a table with the encoding will work much better. The function can easily be converted to an inline-table valued function using a tally table and the encoding table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
OP using SQL 2014 - m0st likely syntax used won't work
- frederico_fonseca wrote:
OP using SQL 2014 - m0st likely syntax used won't work
Are you referring to 'Drop Table If Exists'?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeffrey Williams,
Almost case, replace function replace done but in some case do not fail.
Function:
ALTER Function [dbo].[test_uni_vni_replace] (
@string nvarchar(max)
)
Returns nvarchar(max)
As
Begin
Select @string = replace(@string collate Latin1_General_CS_AS, viet, vni)
From dbo.viet_character_encoding;
Return @string;
End
Example:
declare @input nvarchar(max) = N'Ði'
declare @output nvarchar(max) = dbo.test_uni_vni_replace(@input)
select @input Input,@output Output
Result:
Input Outphut
Ði Ñ i
-
The original function was to convert from VNI to VIET. You changed this to convert from VIET to VNI. Here are the results from my system:
This is the expected result when converting the VIET character 'Ð' to VNI.
What issue are you having with this?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi,
This function have error in some words.
Example:
But this result is meaningless
I don't know why this error.
Kindly help me resolve it. Tks.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply