SQL Server : RegEx ASCII removal

155 views Asked by At

Looking for a way to make a case-sensitive search.

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @RemoveFollowing VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @RemoveFollowing =  '%['+@RemoveFollowing+']%'
    WHILE PatIndex(@RemoveFollowing, @String) > 0
        SET @String = Stuff(@String, PatIndex(@RemoveFollowing, @String), 1, '')
    RETURN @String
END
GO

SELECT dbo.fn_StripCharacters('RoaR! rings the roll', '\R')
  • Supplied input: 'RoaR! rings the roll'

  • Expected output: 'oa! rings the roll'

  • Output received: 'oa! ings the oll'

RegEx tried before (no success): '\R', '\x52', '\n42', '\u0052', 'R'.

I have also tried to Collate the database, and no result:

COLLATE Latin1_General_100_CS_AS_SC;

Microsoft SQL Server Management Studio 12.0.4100.1

Microsoft .NET Framework 4.0.30319.34011

1

There are 1 answers

7
Sean Lange On BEST ANSWER

Why are you using a loop? All you are trying to do is replace a certain letter with an empty string? You need to specify the collation for your input to make it case sensitive.

select replace('RoaR! rings the roll' COLLATE Latin1_General_CS_AS, 'R', '')