September 17, 2015 at 6:48 am
Hi,
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
DECLARE @letters TABLE (Letter NVARCHAR(10))
INSERT INTO @letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@letters
In this case, the "α" is being replaced, but so are "a" and "A".
I have tried changing the datatype from varchar to nvarchar and also changing the collation.
I'd be very grateful for any suggestions you might have.
September 17, 2015 at 6:57 am
That's because the alpha is a unicode character and you're using ascii characters. In this case, the alpha gets changed into an a.
Here's a little change in your code:
DECLARE @letters TABLE (Letter NVARCHAR(10))
INSERT INTO @letters VALUES ('a'), ('A'), ('b'), ('B'), (N'a')
SELECTLetter, REPLACE(Letter,N'a','alpha')
FROM@letters
September 17, 2015 at 7:31 am
That works. Thanks for the quick response! 🙂
September 17, 2015 at 7:32 am
Would a binary collation on the REPLACE do the trick?
September 17, 2015 at 8:29 am
Kristen-173977 (9/17/2015)
Would a binary collation on the REPLACE do the trick?
The problem wasn't on the collation, even with collation Latin1_General_100_CI_AI (Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive) the alpha and the a are considered different characters as long as they're both unicode characters.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply