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