SQL Replace Multi chars with Single-CodePudding

I want to replace all numbers in SQL with a single * value. I've researched the issue, but been unable to replicate what I've found. I also feel like I'm over complicating this process. Here is the break down of what I'm doing currently:

Create temp table Replacing numbers with *.
This is the part I need help with. I need to replace multiple characters with a single *.
Final step is querying my data and GROUPING my data.

Query:

CREATE TABLE #CMS_ERROR_LOG 
(
    ERROR_LOG_BODY NVARCHAR(MAX),
);

INSErT INTO #CMS_ERROR_LOG
select REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE ([ERROR_LOG_BODY], '0', '*'),
'1', '*'),
'2', '*'),
'3', '*'),
'4', '*'),
'5', '*'),
'6', '*'),
'7', '*'),
'8', '*'),
'9', '*')
FROM CMS_ERROR_LOG 
WHERE ERROR_LOG_MESSAGE = 'API Request Capture' 

SELECT
SUBSTRING([ERROR_LOG_BODY],1,CHARINDEX(',', [ERROR_LOG_BODY])-2) AS 'Request'
FROM #CMS_ERROR_LOG 
GROUP BY SUBSTRING([ERROR_LOG_BODY],1,CHARINDEX(',', [ERROR_LOG_BODY])-2)

I read something about using # in the replace statement to only replace with single char but unable to get it to work.

Current Result: [POST] URL: https://localhost:*****/api/authentication/authenticat

Expected result: [POST] URL: https://localhost:*/api/authentication/authenticat

Update: I've been able to format the data a little closer to what I need:

--DROP TABLE #CMS_ERROR_LOG

CREATE TABLE #CMS_ERROR_LOG 
(
    ERROR_LOG_BODY NVARCHAR(MAX),
);

INSErT INTO #CMS_ERROR_LOG
select 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE 
([ERROR_LOG_BODY], '0', '*'),'1', '*'),'2', '*'),'3', '*'),'4', '*'),'5', '*'),'6', '*'),'7', '*'),'8', '*'),'9', '*')
FROM CMS_ERROR_LOG 
WHERE ERROR_LOG_MESSAGE = 'API Request Capture' 

--CLEAN UP
INSERT INTO #CMS_ERROR_LOG
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE 
([ERROR_LOG_BODY], '**', '*'),'***', '*'),'****', '*'),'*****', '*'),'******', '*'),'*******', '*'),'********', '*'),'*********', '*')
FROM #CMS_ERROR_LOG

INSERT INTO #CMS_ERROR_LOG
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE 
([ERROR_LOG_BODY], 'cases/*******', 'cases/*'),'contacts/******', 'contacts/*'),'LoanNumber=**********', 'LoanNumber=*'),'CourtCaseNumber=**', 'CourtCaseNumber=*'),'PropertyAddress=****', 'PropertyAddress=*'),'cases/**', 'cases/*'),'********', '*'),'*********', '*')
FROM #CMS_ERROR_LOG

SELECT
SUBSTRING([ERROR_LOG_BODY],1,CHARINDEX(',', [ERROR_LOG_BODY])-2) AS 'Request'
FROM #CMS_ERROR_LOG 
GROUP BY SUBSTRING([ERROR_LOG_BODY],1,CHARINDEX(',', [ERROR_LOG_BODY])-2)

DROP TABLE #CMS_ERROR_LOG

The problem is, its still not cleaning up all the strings properly: For example I end up with things like this:

[GET] URL: https://masked.../masked.../api/cases/*****/contacts/*

[GET] URL: https://masked.../masked.../api/casefiles/******/loanRecords

UPDATED: I've updated my SQL. I'm able to resolve my issue. Just trying to clean up non-redacted data now. Be nice to have a better way to replace. However, I'm using version 2012 and TRANSLATE is not an option.

New Query:

--DROP TABLE #CMS_ERROR_LOG

CREATE TABLE #CMS_ERROR_LOG 
(
    ERROR_LOG_BODY NVARCHAR(MAX),
);

INSErT INTO #CMS_ERROR_LOG
select 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE 
([ERROR_LOG_BODY], '0', ''),'1', ''),'2', ''),'3', ''),'4', ''),'5', ''),'6', ''),'7', ''),'8', ''),'9', '')
FROM CMS_ERROR_LOG 
WHERE ERROR_LOG_MESSAGE = 'API Request Capture' 

--CLEAN UP
INSERT INTO #CMS_ERROR_LOG
SELECT
REPLACE
([ERROR_LOG_BODY], '//', '/*/')
FROM #CMS_ERROR_LOG

INSERT INTO #CMS_ERROR_LOG
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
([ERROR_LOG_BODY], 'https:/*/', 'https://'),'=&', '=*&'),'OfficeCode=NJ', 'OfficeCode=*'),'OfficeCode=VA', 'OfficeCode=*'),'OfficeCode=NY', 'OfficeCode=*')
FROM #CMS_ERROR_LOG

--Office Codes
INSERT INTO #CMS_ERROR_LOG
SELECT
REPLACE(REPLACE(REPLACE(REPLACE
([ERROR_LOG_BODY], 'OfficeCode=NJ', 'OfficeCode=*'),'OfficeCode=VA', 'OfficeCode=*'),'OfficeCode=NY', 'OfficeCode=*'),'OfficeCode=FL', 'OfficeCode=*')
FROM #CMS_ERROR_LOG

INSERT INTO #CMS_ERROR_LOG
SELECT
REPLACE(REPLACE(REPLACE(REPLACE
([ERROR_LOG_BODY], 'officeCode=NJ', 'OfficeCode=*'),'officeCode=VA', 'OfficeCode=*'),'officeCode=NY', 'OfficeCode=*'),'officeCode=FL', 'OfficeCode=*')
FROM #CMS_ERROR_LOG

SELECT
SUBSTRING([ERROR_LOG_BODY],1,CHARINDEX(',', [ERROR_LOG_BODY])-2) AS 'Request'
FROM #CMS_ERROR_LOG 
GROUP BY SUBSTRING([ERROR_LOG_BODY],1,CHARINDEX(',', [ERROR_LOG_BODY])-2)

DROP TABLE #CMS_ERROR_LOG

CodePudding user response：

If you are on SQL Server 2017 you have our good friend TRANSLATE. Otherwise a nested REPLACE will do just fine. In these examples I'm populating a table with NEWID's for random text with numbers, then returning what you want using both techniques. (Note the re-producible sample data):

--==== 1. Re-producable Sample Data
DECLARE @t TABLE (SampleTxt VARCHAR(36));
INSERT @t SELECT TOP(10) NEWID() FROM sys.all_columns;

--==== 2. Solution #1: TRANSLATE 
SELECT 
  Original = t.SampleTxt,
  Cleaned = REPLACE(REPLACE(REPLACE(REPLACE(TRANSLATE(t.SampleTxt,'1234567890',
                    '**********'),'********','*'),'****','*'),'**','*'),'**','*')
FROM @t AS t;

--==== 3. Solution #2: Nested Replace for Number replacement
SELECT 
  Original = t.SampleTxt,
  Cleaned  = Transform2.Txt
FROM        @t AS t
CROSS APPLY(VALUES(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                    REPLACE(REPLACE (t.SampleTxt,'0','*'),'1','*'),'2','*'),'3','*'),
                    '4', '*'),'5', '*'),'6','*'),'7','*'),'8','*'),'9','*'))
                   ) AS Transform1(Txt)
CROSS APPLY (VALUES(REPLACE(REPLACE(REPLACE(REPLACE(Transform1.Txt,
                    '********','*'),'****','*'),'**','*'),'**','*'))) AS Transform2(Txt);

Both return:

Original                               Cleaned
-------------------------------------- ------------------------------------
EF73E87F-868D-49E4-A347-CA42D5A378C0   EF*E*F-*D-*E*-A*-CA*D*A*C*
092CDE6E-C844-416A-B2DC-ACA948A38991   *CDE*E-C*-*A-B*DC-ACA*A*
D157AF76-35C0-401A-9CCC-5AC3F522A91B   D*AF*-*C*-*A-*CCC-*AC*F*A*B
00A0BD46-3A97-452E-806D-D5272C0BDF1C   *A*BD*-*A*-*E-*D-D*C*BDF*C
837B469C-7453-4B77-894B-9AEEDFFEF543   *B*C-*-*B*-*B-*AEEDFFEF*
9C92AE57-4AF5-4E82-A3C2-6D94DF9E3C3F   *C*AE*-*AF*-*E*-A*C*-*D*DF*E*C*F
58D8D873-D9E0-4582-867B-16810F10BD42   *D*D*-D*E*-*-*B-*F*BD*
7F80B552-C1D6-4A97-9C51-6FB8491D26B6   *F*B*-C*D*-*A*-*C*-*FB*D*B*
C466CADC-E38F-43A2-AA8F-CB0BDB0069DB   C*CADC-E*F-*A*-AA*F-CB*BDB*DB
1A5D6EC0-8335-485C-927F-D36E019EA6E1   *A*D*EC*-*-*C-*F-D*E*EA*E*