Help with replacing Superscript 2

• mpdillon

  Hall of Fame

  Points: 3566

  More actions

  June 25, 2011 at 11:47 am

  #253390

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 25, 2011 at 1:45 pm

  #1344140

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 25, 2011 at 1:53 pm

  #1344141

  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

• mpdillon

  Hall of Fame

  Points: 3566

  More actions

  June 25, 2011 at 2:03 pm

  #1344143

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 25, 2011 at 2:23 pm

  #1344144

  You're welcome

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato