CODEWITHSUNDEEP
sql-server
barlop
barlop

Reputation: 13780

In SQL Server Management Studio, SQL Server fails on hebrew diacritical marks

In SMSS - SQL Server Management Studio, SQL Server fails on hebrew vowels

I am using SMSS v17.3, select @@version shows 

Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Jun 10 2015 03:35:45 
Copyright (c) Microsoft Corporation 
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

I have the following code

DECLARE @bbb1 NVARCHAR(50);
DECLARE @bbb2 NVARCHAR(50);

SET @bbb1 = REPLACE(N'אאא', N'א', N'ב');
SET @bbb2 = REPLACE(N'אָאָאָ', N'א', N'ב');

SELECT @bbb1;
SELECT @bbb2;

enter image description here

This is the hebrew letter aleph א (\u05D0). This is the hebrew letter bet ב (\u05D1) Below the letters are some markings, diacritical marks. When you see one below a letter, it is following the letter. They can be entered by going into character map, finding e.g. \u05B8 and pasting it in.

As you can see, the statement 

set @bbb1 = REPLACE(N'אאא', N'א', N'ב');

works

There are no diacritical marks in any parameter there.

The statement 

set @bbb2 = REPLACE(N'אָאָאָ', N'א', N'ב');

has diacritical marks in the first parameter, and it fails.

The result it should give is בָבָבָ i.e. changing every aleph (א) to bet(ב) and leave the diacritical marks as they are. But what it returns is the original string, it replaces nothing. It doesn't find any aleph (א) in the original string even though they are clearly there.

Upvotes: 2

Views: 258

Answers (1)

dfundako
dfundako

Reputation: 8314

Diacritics suck. This solution should work for your purposes. It makes a staging table, declares your string, splits the accent from the base character, replaces the character, and smashes it all back together. 

DECLARE @table TABLE (letter NVARCHAR(10)) --Stage table to work with
DECLARE @letters NVARCHAR(100) = N'אָאָאָ' --string to use

DECLARE @length INT = LEN(@letters)
DECLARE @counter INT = 0

--loop to break the @letters string into rows in our stage table
WHILE @counter < @length+1
BEGIN 
INSERT INTO @table
SELECT SUBSTRING(@letters, @counter, 1)
SET @counter = @counter + 1
END

--Take the rows from our stage table, replace the old char with the new char, 
----and then concat them together with the accent
DECLARE @formatted_final NVARCHAR(4000) 
SELECT @formatted_final = COALESCE(@formatted_final + '', '') + REPLACE(letter, N'א', N'ב') 
FROM @table

SELECT @formatted_final

Upvotes: 1

Related Questions