February 6, 2017 at 1:03 pm
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'
February 6, 2017 at 5:10 pm
briancampbellmcad - Monday, February 6, 2017 12:40 PMIO gave this a try but got an incorrect syntax error:USE [NBCC_Search]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCreate Function [dbo].[fn_iReplaceSpecialCharacters] (@TempString AS NVARCHAR(150))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECTREPLACE(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 NewStringGO
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
February 7, 2017 at 10:22 am
Sergiy - Monday, February 6, 2017 5:10 PMbriancampbellmcad - Monday, February 6, 2017 12:40 PMIO gave this a try but got an incorrect syntax error:USE [NBCC_Search]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCreate Function [dbo].[fn_iReplaceSpecialCharacters] (@TempString AS NVARCHAR(150))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECTREPLACE(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 NewStringGO
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
GOYou may use Romanian case-sensitive collation instead on Latin.
This works great! Thank You!!
Viewing 3 posts - 31 through 33 (of 33 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