SQL - Reaplce Multi with Single Char

Micah Holmes 121 Reputation points
2022-04-11T18:13:59.16+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,273 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 106K Reputation points MVP
    2022-04-11T21:30:17.82+00:00

    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('        <frame procname="adhoc" line="1" stmtstart="58" stmtend="176" sqlhandle="0x020000009750ee1a21ebad245e33425c32f24425380c88460000000000000000000000000000000000000000">
    unknown    </frame>
            <frame procname="adhoc" line="1" stmtend="128" sqlhandle="0x020000004fbcd10dbcde5101404fb76a70f4729acd4e17c20000000000000000000000000000000000000000">')
    
    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
    
            <frame procname="adhoc" line="*" stmtstart="*" stmtend="*" sqlhandle="*x*ee*a*ebad*e*c*f*c*">
    unknown    </frame>
            <frame procname="adhoc" line="*" stmtend="*" sqlhandle="*x*fbcd*dbcde*fb*a*f*acd*e*c*">
    
    1 person found this answer helpful.
    0 comments No comments

  2. Viorel 114.4K Reputation points
    2022-04-11T18:51:22.93+00:00

    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.


  3. LiHong-MSFT 10,046 Reputation points
    2022-04-12T03:23:33.07+00:00

    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.

    0 comments No comments

  4. Tom Phillips 17,721 Reputation points
    2022-04-12T13:59:58.533+00:00

    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.

    0 comments No comments