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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy