June 25, 2011 at 11:47 am
I am converting a scientific print catalog to be diplayed on the web. I would have been using the T-SQL function REPLACE to change ASCII special characters into a friendly web format. The ASCII character code for Superscript 2 is 178.
When I try to replace the Superscript 2 with ² using my normail REPLACE, I get very unexpected results. Not only are all the Superscript 2's replaced but every occurrance of the numeral 2 is replaced.
How should I use T-SQL to replace the Superscript 2 with ²?
Here is a test table and SQL command to verify my results.
CREATE TABLE STATEMENT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TwoTable](
[TestString] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ADD DATA TO TABLE
Insert INto TwoTable(TestString)
select '1'
union
select '2'
union
Select '3'
Trying to replace the nonexsitant Superscript 2.
UPDATE TwoTable
SET TestString = REPLACE(TestString, NCHAR(178), '²')
My result: The character 2 is replaced with ²
Thanks,
pat
June 25, 2011 at 1:45 pm
A conversion is happening because you're asking to replace an NCHAR in a VARCHAR column.
--CREATE TABLE STATEMENT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TwoTable]
(
[TestString] [varchar](50) NULL
)
ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--ADD DATA TO TABLE
INSERT INTO TwoTable
(
TestString
)
SELECT '1'
UNION
SELECT '2'
UNION
SELECT '3'
SELECT *,
ASCII(teststring)
FROM dbo.TwoTable
UPDATE TwoTable
SET TestString = REPLACE(TestString, CHAR(178), '²')
SELECT *,
ASCII(teststring)
FROM dbo.TwoTable
UPDATE TwoTable
SET TestString = REPLACE(TestString, NCHAR(178), '²')
SELECT *,
ASCII(teststring)
FROM dbo.TwoTable
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2011 at 1:53 pm
In the case of all Unicode data you can use a binary collation to prevent the overlap of the number 2 with its superscript counterpart.
--CREATE TABLE STATEMENT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TwoTable]
(
[TestString] [nvarchar](50) NULL
)
ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--ADD DATA TO TABLE
INSERT INTO TwoTable
(
TestString
)
SELECT N'1'
UNION
SELECT N'2'
UNION
SELECT N'3'
SELECT *,
ASCII(teststring)
FROM dbo.TwoTable
UPDATE TwoTable
SET TestString = REPLACE(TestString COLLATE Latin1_General_100_BIN, NCHAR(178), N'²')
SELECT *,
ASCII(teststring)
FROM dbo.TwoTable
UPDATE TwoTable
SET TestString = REPLACE(TestString, NCHAR(178), N'²')
SELECT *,
ASCII(teststring)
FROM dbo.TwoTable
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2011 at 2:03 pm
Thank you. I thought it would be something along those lines. I am using the NChar for the replace because once I tried Replace(Teststring,Char(8218),',') and that set all the field values to NULL. So it was suggested to me to use the NChar which I did and everything was going fine until now. Apparently, I have not used NChar correctly.
Thanks,
pat
June 25, 2011 at 2:23 pm
You're welcome 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply