modifying a 'replace' function to use Lower and Upper

Question

modifying a 'replace' function to use Lower and Upper

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 1

Here's a start from the above post, but this has a lot of new concepts to me so lots of errors are popping up so this obviously needs some work coach:

ALTER FUNCTION [dbo].[fn_ReplaceSpecialCharacters] (@OriginalText VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
Tally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),HighAsciiReplaced
AS
(
select STUFF(
(
SELECT
CASE

WHEN ASCII(SUBSTRING(@TempString,N,1)) = 536 THEN 'S'
WHEN ASCII(SUBSTRING(@TempString,N,1)) = 537 THEN 's'
WHEN ASCII(SUBSTRING(@TempString,N,1)) = 536 THEN 'T'
WHEN ASCII(SUBSTRING(@TempString,N,1)) = 537 THEN 't'

ELSE SUBSTRING(@OriginalText,Tally.N,1)
END
FROM Tally
WHERE Tally.N <= len(@OriginalText)
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') as CleanedText
)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Does something like this works?
Create Function [dbo].[fn_iReplaceSpecialCharacters] (@TempString AS VARCHAR(100)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(@TempString COLLATE Latin1_General_CS_AI ,N'?', N'S') ,N'?', N's') ,'T', 'T') ,'t', 't') ,'A', 'A') ,'a', 'a') ,'E', 'E') ,'e', 'e') ,'I', 'I') ,'i', 'i') ,'O', 'O') ,'o', 'o') ,'U', 'U') ,'u', 'u') NewString GO DECLARE @Sample TABLE(Strings nvarchar(100)); INSERT INTO @Sample VALUES( N'ÀAaáàlbèert ËEîin?s?têeiìn ÌInstìitúutëe'), (N'Buchare?t')

SELECT * FROM @Sample CROSS APPLY fn_iReplaceSpecialCharacters( Strings)x;

GO DROP FUNCTION fn_iReplaceSpecialCharacters;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 3

I used the above example to create another function with no problems until testing it:

Create Function [dbo].[fn_iReplaceSpecialCharacters] (@TempString AS VARCHAR(100))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT replace(replace(
replace(replace(@TempString COLLATE Latin1_General_CS_AI
,N'?', N'S')
,N'?', N's')
,N'?', N'T')
,N'?', N't') NewString
GO

DECLARE @Sample TABLE(Strings nvarchar(100));

INSERT INTO @Sample
VALUES( N'ÀAaáàlbèert ËEîin?s?têeiìn ÌInstìitúutëe')

SELECT *
FROM @Sample
CROSS APPLY fn_iReplaceSpecialCharacters( Strings)x;

GO

Running this I get: ÀAaáàlbèert ËEîin?s?têeiìn ÌInstìitúutëe

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

I thought of creating a view such as:

USE [NBCC_Search]
GO
DROP VIEW [dbo].[SpecialRomanian]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[SpecialRomanian]
AS

SELECT [ContactId]
,[FirstName]
,[LastName]

,CASE

WHEN ASCII(SUBSTRING([LastName],CHARINDEX('?',[LastName]),1)) = 536 THEN 'S'
WHEN ASCII(SUBSTRING([LastName],CHARINDEX('?',[LastName]),1)) = 537 THEN 's'
WHEN ASCII(SUBSTRING([LastName],CHARINDEX('?',[LastName]),1)) = 538 THEN 'T'
WHEN ASCII(SUBSTRING([LastName],CHARINDEX('?',[LastName]),1)) = 539 THEN 't'

END

     AS 'NewName'

FROM [dbo].[Professional] where country = 'Romania'

GO

But 'NewName' shows only NULL values

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

briancampbellmcad - Thursday, February 2, 2017 2:33 PM
I thought of creating a view such as:
USE [NBCC_Search]
GO
DROP VIEW [dbo].[SpecialRomanian]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SpecialRomanian]
AS
SELECT [ContactId]
  ,[FirstName]
  ,[LastName]
,CASE
WHEN ASCII(SUBSTRING([LastName],CHARINDEX('È˜',[LastName]),1)) = 536 THEN 'S'
WHEN ASCII(SUBSTRING([LastName],CHARINDEX('È™',[LastName]),1)) = 537 THEN 's'
WHEN ASCII(SUBSTRING([LastName],CHARINDEX('Èš',[LastName]),1)) = 538 THEN 'T'
WHEN ASCII(SUBSTRING([LastName],CHARINDEX('È›',[LastName]),1)) = 539 THEN 't'
END

     AS 'NewName'

FROM [dbo].[Professional] where country = 'Romania'
GO
But 'NewName' shows only NULL values

Could you post the DDL of dbo.Professional?

If it's a view, could you also post the DDL of the underlying tables?

Cheers!

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 6

DDL of the Table from which I attempted a view:

USE [NBCC_Search]
GO

/****** Object: Table [dbo].[Professional] Script Date: 2/2/2017 4:40:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Professional](
    [ContactId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](150) NULL,
    [LastName] [varchar](150) NULL,
    [City] [varchar](150) NULL,
    [State] [varchar](70) NULL,
    [PostalCode] [varchar](10) NULL,
    [Country] [varchar](150) NULL,
    [Phone] [varchar](25) NULL,
    [Certifications] [varchar](1000) NULL,
    [Areas] [varchar](1000) NULL,
    [Languages] [varchar](1000) NULL,
    [Longitude] [decimal](18, 9) NULL,
    [Latitude] [decimal](18, 9) NULL,
    [OriginalDatabase] [varchar](100) NULL,
    [OriginalDatabaseId] [varchar](50) NULL,
    [RegionId] [int] NULL,
    [Email] [varchar](250) NULL,
    [InsertDate] [datetime] NOT NULL CONSTRAINT [DF__Professio__Inser__7D78A4E7] DEFAULT (getdate()),
    [IsOnCounselorFind] [bit] NOT NULL CONSTRAINT [DF_Professional_IsOnCounselorFind] DEFAULT ((1)),
    [ExpirationString] [varchar](25) NULL,
CONSTRAINT [PK__Professional__7C8480AE] PRIMARY KEY CLUSTERED
(
    [ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Professional] WITH CHECK ADD CONSTRAINT [FK_Professional_Regional] FOREIGN KEY([RegionId])
REFERENCES [dbo].[Region] ([RegionId])
GO

ALTER TABLE [dbo].[Professional] CHECK CONSTRAINT [FK_Professional_Regional]
GO

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 7

briancampbellmcad - Thursday, February 2, 2017 2:41 PM
DDL of the Table from which I attempted a view:
USE [NBCC_Search]
GO
/****** Object: Table [dbo].[Professional] Script Date: 2/2/2017 4:40:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Professional](
    [ContactId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](150) NULL,
    [LastName] [varchar](150) NULL,
    [City] [varchar](150) NULL,
    [State] [varchar](70) NULL,
    [PostalCode] [varchar](10) NULL,
    [Country] [varchar](150) NULL,
    [Phone] [varchar](25) NULL,
    [Certifications] [varchar](1000) NULL,
    [Areas] [varchar](1000) NULL,
    [Languages] [varchar](1000) NULL,
    [Longitude] [decimal](18, 9) NULL,
    [Latitude] [decimal](18, 9) NULL,
    [OriginalDatabase] [varchar](100) NULL,
    [OriginalDatabaseId] [varchar](50) NULL,
    [RegionId] [int] NULL,
    [Email] [varchar](250) NULL,
    [InsertDate] [datetime] NOT NULL CONSTRAINT [DF__Professio__Inser__7D78A4E7] DEFAULT (getdate()),
    [IsOnCounselorFind] [bit] NOT NULL CONSTRAINT [DF_Professional_IsOnCounselorFind] DEFAULT ((1)),
    [ExpirationString] [varchar](25) NULL,
CONSTRAINT [PK__Professional__7C8480AE] PRIMARY KEY CLUSTERED
(
    [ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Professional] WITH CHECK ADD CONSTRAINT [FK_Professional_Regional] FOREIGN KEY([RegionId])
REFERENCES [dbo].[Region] ([RegionId])
GO
ALTER TABLE [dbo].[Professional] CHECK CONSTRAINT [FK_Professional_Regional]
GO

Thanks!

The LastName column, which is what you were evaluating the function against, is VARCHAR.

Are we sure that the LastName column contains those unicode characters?

Cheers!

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 8

This is a database whose table list names, cities, streets, etc. using the Romanian alphabet, so it is rife with these four characters.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

briancampbellmcad - Thursday, February 2, 2017 3:05 PM
This is a database whose table list names, cities, streets, etc. using the Romanian alphabet, so it is rife with these four characters.

I understand that.

My point was that those characters shouldn't even display in a VARCHAR column, so there's likely something else going on if you're running a SELECT against a VARCHAR column and somehow seeing those unicode characters.

Could you maybe post a screenshot showing a SELECT from that column returning those characters?

I haven't been able to reproduce that at all on my end.

Cheers!

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 10

DaveBriCam

SSCarpal Tunnel

Points: 4294

February 3, 2017 at 7:06 am

#1926578

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 11

I may have stumbled on the answer you mentioned about nvarchar vs. varchar... here with nvarchar in another table it renders properly:

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 12

Ok, excellent.

This all makes sense now.

That former screenshot reflects what I expected. Those problematic Romanian letters are NOT in the column in question (I probably didn't express it well enough the first time, but that was the thrust of my question; I knew you were dealing with Romanian names and such, but I couldn't see how those characters could be present if all the string-type columns in that table were VARCHAR).

For that table to correctly store those characters, the columns will have to be switched to NVARCHAR. That still won't fix the existing data, though; that would have to be a separate process.

Hopefully there's a good source from which those can be corrected.

Cheers!

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 13

I changed my fields to nvarchar but it appears maybe that 'REPLACE' has its limits... should I be using another function that uses Unicode? Any T-SQL examples?

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 14

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

DesNorton SSC-Insane Points: 24284 More actions · Answer 15

briancampbellmcad - Monday, February 6, 2017 11:05 AM
I changed my fields to nvarchar but it appears maybe that 'REPLACE' has its limits... should I be using another function that uses Unicode? Any T-SQL examples?

You can use a lookup table to add/remove characters to replace.

/* Create a table to hold the list of characters that need to be replaced */

CREATE TABLE dbo.tb_ReplaceChars ( UnicodeChar NCHAR(1) COLLATE Latin1_General_BIN NOT NULL , AsciiChar CHAR(1) COLLATE Latin1_General_BIN NOT NULL , CONSTRAINT PK_ReplaceChars PRIMARY KEY CLUSTERED (UnicodeChar) );


/* Create a function do the replacements, and return a cleaned string */
CREATE FUNCTION dbo.fn_ReplaceChars(@UnicodeString NVARCHAR(4000))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
 WITH
  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)
           , (0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
 , Nums(N) AS (SELECT TOP (LEN(@UnicodeString)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
     FROM T T1, T T2, T T3
    )
 SELECT AsciiString = (
  SELECT ISNULL(rc.AsciiChar, SUBSTRING(@UnicodeString, p.N, 1))
  FROM Nums AS p
  LEFT JOIN dbo.tb_ReplaceChars AS rc
   ON SUBSTRING(@UnicodeString, p.N, 1) = rc.UnicodeChar
  ORDER BY p.N
  FOR XML PATH(''), TYPE
  ).value('(./text())[1]', 'VARCHAR(8000)')

/* Insert some characters that need to be replaced */

INSERT INTO dbo.tb_ReplaceChars (UnicodeChar, AsciiChar)
VALUES (N'È˜', 'S')
, (N'È™', 's')
, (N'Èš', 'T')
, (N'È›', 't');

/* Test the code */

DECLARE @MyBadString NVARCHAR(4000) = N'È˜È™ÈšÈ› Ã€AlbÃ¨ert Ã‹EÃ®instÃªeiÃ¬n ÃŒInstÃ¬itÃºutÃ«e';

SELECT OrigString = @MyBadString
, AsciiString

FROM dbo.fn_ReplaceChars(@MyBadString);

/* Insert some more characters that need to be replaced */
INSERT INTO dbo.tb_ReplaceChars (UnicodeChar, AsciiChar)
VALUES (N'Ã€', 'A')
, (N'Ã‹', 'E')
, (N'Ã¨', 'e');

/* and test again ... */
DECLARE @MyBadString NVARCHAR(4000) = N'È˜È™ÈšÈ› Ã€AlbÃ¨ert Ã‹EÃ®instÃªeiÃ¬n ÃŒInstÃ¬itÃºutÃ«e';

SELECT OrigString = @MyBadString
, AsciiString
FROM dbo.fn_ReplaceChars(@MyBadString);

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