replace multiple values at the same time – in order to convert a string to a number

I can think of two approaches.

The first is to use a bunch of nested replace() statements:

select replace(replace(replace(col, '$', ''), '£', ''), 'n/a', '')

and so on.

The second is to find the first digit and try converting from there. This requires complicated logic with patindex(). Here is an example:

select cast(left(substring(col, patindex('%[0-9]%', col), 1000),
                 patindex('%[^0-9]%', substring(col, patindex('%[0-9]%', col), 1000)) - 1
                ) as int)

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)