question

MicahHolmes-1650 avatar image
0 Votes"
MicahHolmes-1650 asked LiHongMSFT-3908 commented

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-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

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

@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 Votes 0 ·

@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 Votes 0 ·

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 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

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

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

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

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 MicahHolmes-1650 ·

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


0 Votes 0 ·

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 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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

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

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

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

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

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

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