modifying a 'replace' function to use Lower and Upper

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  February 6, 2017 at 1:03 pm

  #1926949

  briancampbellmcad - Friday, February 3, 2017 7:06 AM

  

  If the fields in this screenshot are declared as NVARCHAR, it shows that the data was entered in the form 'Unicode Data', instead of N'Unicode Data'

  ---

  How to post data/code on a forum to get the best help.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  February 6, 2017 at 5:10 pm

  #1927002

  briancampbellmcad - Monday, February 6, 2017 12:40 PM

  IO gave this a try but got an incorrect syntax error:

  USE [NBCC_Search]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO

  Create Function [dbo].[fn_iReplaceSpecialCharacters] (@TempString AS NVARCHAR(150))
  RETURNS TABLE WITH SCHEMABINDING
  AS RETURN
  SELECT

  REPLACE(REPLACE(
  REPLACE(REPLACE(cast(
  column Collate Latin1_General_CS_AS as nvarchar(150)),
  CHAR(83),N'Ș'),
  CHAR(115), N'È™'),
  CHAR(84),N'Èš'),
  CHAR(116),N'È›') AS NewString

  GO

  Yes, the syntax is incorrect.

  This one works fine on my machine:

  alter Function [dbo].[fn_ReplaceSpecialCharacters] (@TempString AS NVARCHAR(100) )
RETURNS VARCHAR(100)
AS
BEGIN

      SELECT @TempString = REPLACE (@TempString COLLATE Latin1_General_BIN, SpecChar, CommonChar)
    FROM (
        SELECT N'Ș' COLLATE Latin1_General_BIN, 'S' COLLATE Latin1_General_BIN
        UNION
        SELECT N'È™' COLLATE Latin1_General_BIN, 's' COLLATE Latin1_General_BIN
        UNION
        SELECT N'Èš' COLLATE Latin1_General_BIN, 't' COLLATE Latin1_General_BIN
        UNION
        SELECT N'È›' COLLATE Latin1_General_BIN, 't' COLLATE Latin1_General_BIN
        ) R (SpecChar , CommonChar )
RETURN @TempString

  END

  GO

  SELECT [ContactId],[FirstName],[LastName]
,[dbo].[fn_ReplaceSpecialCharacters]([LastName] ) AS 'NewName'
FROM (SELECT country = 'Romania', [ContactId] = 1, FirstName = 'brian', lastname = N'Buchareșt') P
where country = 'Romania' order by [LastName] desc
GO


  You may use Romanian case-sensitive collation instead on Latin.

  _____________
  Code for TallyGenerator

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  February 7, 2017 at 10:22 am

  #1927145

  Sergiy - Monday, February 6, 2017 5:10 PM

  briancampbellmcad - Monday, February 6, 2017 12:40 PM

  IO gave this a try but got an incorrect syntax error:

  USE [NBCC_Search]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO

  Create Function [dbo].[fn_iReplaceSpecialCharacters] (@TempString AS NVARCHAR(150))
  RETURNS TABLE WITH SCHEMABINDING
  AS RETURN
  SELECT

  REPLACE(REPLACE(
  REPLACE(REPLACE(cast(
  column Collate Latin1_General_CS_AS as nvarchar(150)),
  CHAR(83),N'Ș'),
  CHAR(115), N'È™'),
  CHAR(84),N'Èš'),
  CHAR(116),N'È›') AS NewString

  GO

  Yes, the syntax is incorrect.

  This one works fine on my machine:

  alter Function [dbo].[fn_ReplaceSpecialCharacters] (@TempString AS NVARCHAR(100) )
RETURNS VARCHAR(100)
AS
BEGIN

      SELECT @TempString = REPLACE (@TempString COLLATE Latin1_General_BIN, SpecChar, CommonChar)
    FROM (
        SELECT N'Ș' COLLATE Latin1_General_BIN, 'S' COLLATE Latin1_General_BIN
        UNION
        SELECT N'È™' COLLATE Latin1_General_BIN, 's' COLLATE Latin1_General_BIN
        UNION
        SELECT N'Èš' COLLATE Latin1_General_BIN, 't' COLLATE Latin1_General_BIN
        UNION
        SELECT N'È›' COLLATE Latin1_General_BIN, 't' COLLATE Latin1_General_BIN
        ) R (SpecChar , CommonChar )
RETURN @TempString

  END

  GO

  SELECT [ContactId],[FirstName],[LastName]
,[dbo].[fn_ReplaceSpecialCharacters]([LastName] ) AS 'NewName'
FROM (SELECT country = 'Romania', [ContactId] = 1, FirstName = 'brian', lastname = N'Buchareșt') P
where country = 'Romania' order by [LastName] desc
GO


  You may use Romanian case-sensitive collation instead on Latin.

  This works great! Thank You!!