How can I do SQL Server RegEx replace similar to JavaScript RegEx replace

Question

How can perform below shown JavaScript RegEx replace in SQL Server?

var str = "$5,000";
console.log(str.replace(/[$,]/g, ""));

Output:

See stackoverflow.com/questions/13253259/… and maybe stackoverflow.com/a/31693412/3832970. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Oct 18, 2016 at 7:31
No regex needed in this case. SQL server has the money type, and you can cast that to an int or a bigint for large numbers. cast(cast('$5,000' as money) as int) — LukStorms
– LukStorms, Commented Oct 18, 2016 at 7:49

Meena · Accepted Answer · 2016-10-18 09:11:56Z

1

Try this

declare @str money
set @str= cast(cast('$5,000' as money) as int)

Or else if you especially want to use regular expression, you can try the below,

DECLARE @Str varchar(100)
SET @Str = '$5,000' 
set @Str = STUFF(@Str, PATINDEX('%[$,]%', @Str),1, '')
select @str

answered Oct 18, 2016 at 7:55

Meena

71510 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

the result is '5,000' and I want the result as '5000'

Just do a double replace replace(replace(@Str,'$',''),',','') then. PATINDEX only gives the first occurrence of a match.