SQL - Reaplce Multi with Single Char

Question

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

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

Answer

Since translate does not seem to be available to you, I assume that you are on SQL 2016 or earlier. Pity, since on SQL 2017 this could be done with regexp replacement by calling Python from SQL Server. (Although, if you are on SQL 2016, you could to the same in R.(

The below works on SQL 2012 and up. If you are on SQL 2008 or earlier, you need to replace IIF with a plain CASE.

The sample data may look funny. I just took something that I found on my screen.

CREATE TABLE #temp (x nvarchar(MAX))
INSERT #temp(x) VALUES('        
unknown    
        ')

WHILE EXISTS (SELECT * FROM #temp WHERE patindex('%[0-9]%', x) > 0)
BEGIN
  ; WITH CTE AS (
      SELECT x, pos = patindex('%[0-9]%', x)
      FROM   #temp
  ), CTE2 AS (
      SELECT x, lft = substring(x, 1, pos-1), rgt = substring(x, pos, len(x))
      FROM   CTE
      WHERE  pos > 0
  ), CTE3 AS (
      SELECT x, lft, rgt, nextpos = patindex('%[^0-9]%', rgt)
      FROM    CTE2
  )
  UPDATE CTE3
  SET    x = lft + '*' + IIF(nextpos > 0, substring(rgt, nextpos, len(rgt)), '')
END

SELECT * FROM #temp

        
unknown

Answer

To replace digits with '*', try this:

insert into #CMS_ERROR_LOG
select 
    replace(replace(replace(translate(ERROR_LOG_BODY, '0123456789', '**********'), '**', '*' + char(1)), char(1) + '*', char(1)), char(1), '')
from CMS_ERROR_LOG

If translate is not available, then use your series of replace.

Answer

Hi @Micah Holmes
My solution is: First REPLACE all the numbers with spaces ' ', then replace consecutive spaces with individual '*'
Please check this query:

CREATE TABLE #CMS_ERROR_LOG(ERROR_LOG_BODY NVARCHAR(MAX));  
INSERT INTO #CMS_ERROR_LOG VALUES('837B469C74534B77894B9AEEDFFEF543')  
  
;WITH CTE AS  
(  
 SELECT ERROR_LOG_BODY,  
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ERROR_LOG_BODY,'9',' ')  
                          ,'8',' '),'7',' '),'6',' '),'5',' '),'4',' '),'3',' '),'2',' '),'1',' '),'0',' '),' ','<>'),'><',''),'<>','*')AS New_ERROR_LOG_BODY  
 FROM #CMS_ERROR_LOG  
)  
UPDATE CTE  
SET ERROR_LOG_BODY=New_ERROR_LOG_BODY

Hope this helps you.

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

There is not a straight forward way in SQL prior to 2017 to do that kind of string replace. You have to use something like Erland described.

SQL - Reaplce Multi with Single Char

4 answers