How to replace all non-numeric values in a varchar Column with a 0-CodePudding

I'm trying to convert a column table to BIGINT, but first I need to remove all junk data

This means, I have to remove all the non-numeric data in this column and replace this with a zero so I can update the table type

How I can achieve this in SQL Server?

Thanks!

Example:

CREATE TABLE SampleData
(
    Col1 VARCHAR(100)
)

-- I want to remove this kind of data
INSERT INTO SampleData
VALUES('<font style="vertical-align: inherit;"><font style="vertical-align: inherit;">2007220737724</font></')

-- I want to keep this kind of data
INSERT INTO SampleData
VALUES('2007220737724')

CodePudding user response：

It seems simple enough, you can just use try_cast and replace all non valid data with '0'

update t
set col1='0'
where try_cast(col1 as bigint) is null

CodePudding user response：

It appears that your data is almost valid XML, just missing a proper closing tag.

Assuming it was valid, you could use XQuery

SELECT CAST(Col1 AS xml).value('(font/font/text())[1]','bigint')
FROM SampleData

db<>fiddle