SQL - Reaplce Multi with Single Char

Question

question

MicahHolmes-1650 asked Apr 11, '22 LiHongMSFT-3908 commented Apr 29, '22

SQL - Reaplce Multi with Single Char

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-general sql-server-transact-sql

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 · Apr 11 at 06:49 PM

@MicahHolmes-1650,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 ·

YitzhakKhabinsky-0887 · Apr 11 at 07:08 PM

@MicahHolmes-1650,

You failed to provide a minimal reproducible example on the stackoverflow.com:
https://stackoverflow.com/questions/71829201/sql-replace-multi-chars-with-single

Now you are repeating the same mistake here.

You need to edit your question, add all ##1-4 as section headers, and fill them out.

0 ·

LiHongMSFT-3908 · Apr 29 at 06:19 AM

Hi @MicahHolmes-1650
Have you validate all the answers so far and provide any update?
If all of the answers are not working or helpful, please share with us your confusion or more details about this issue.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

0 ·

Answer 1 · 2022-04-11T18:51:22.93Z

Viorel-1 answered Apr 11, '22 Viorel-1 commented Apr 11, '22

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.

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MicahHolmes-1650 · Apr 11 at 06:57 PM

Msg 195, Level 15, State 10, Line 10
'translate' is not a recognized built-in function name.

0 ·

Viorel-1 MicahHolmes-1650 · Apr 11 at 07:04 PM

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

0 ·

MicahHolmes-1650 Viorel-1 · Apr 11 at 07:29 PM

Can you provide sample? Not sure what I'm suppose to add or how I'm suppose to adjust the query to use your logic. Only get two arguments for REPLACE in sql.

0 ·

Show more comments

Answer 2 · 2022-04-11T21:30:17.82Z

ErlandSommarskog answered Apr 11, '22

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*">

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-04-12T03:23:33.07Z

LiHongMSFT-3908 answered Apr 12, '22

Hi @MicahHolmes-1650
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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2022-04-12T13:59:58.533Z

TomPhillips-1744 answered Apr 12, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question