CODEWITHSUNDEEP
regexsql-server-2005
bala nemate
bala nemate

Reputation: 21

RegEx : Replace parts of dynamic strings

I have a string

IsNull(VSK1_DVal.RuntimeSUM,0),

I need to remove IsNull part, so the result would be

VSK1_DVal.RuntimeSUM,

I'm absolute new to RegEx, but it wouldn't be a problem, if not one thing : VSK1 is dynamic part, can be any combination of A-Z,0-9 and any length. How to replace strings with RegEx? I use MSSQL 2k5, i think it uses general set of RegEx rules.

EDIT : I forgot to say, that I'm doing replacement in SSMS Query window's Replace Box (^H) - not building RegEx query

br marius

Upvotes: 0

Views: 920

Answers (4)

Upgradingdave
Upgradingdave

Reputation: 13056

here's a regex that should work: 

[^(]+\(([^,]+),[^)]\)

Then use $1 capture group to extract the part that you need.

I did a sanity check in ruby: 

orig = "IsNull(VSK1_DVal.RuntimeSUM,0),"
regex = /[^(]*\(([^,]+),[^)]\)/
result = orig.sub(regex){$1} # result => VSK1_DVal.RuntimeSUM,

It gets trickier if you have a prefix that you want to retain. Like if you have this: 

"somestuff = IsNull(VSK1_DVal.RuntimeSUM,0),"

In this case, you need someway to identify the start of the pattern. Maybe you can use '=' to identify the start of the pattern? If so, this should work: 

orig = "somestuff = IsNull(VSK1_DVal.RuntimeSUM,0),"
regex = /=\s*\w+\(([^,]+),[^)]\)/
result = orig.sub(regex){$1} # result => somestuff = VSK1_DVal.RuntimeSUM,

But then the case where you don't have an equals sign will fail. Maybe you can use 'IsNull' to identify the start of the pattern? If so, try this (note the '/i' representing case insensitive matching):

orig = "somestuff = isnull(VSK1_DVal.RuntimeSUM,0),"
regex = /IsNull\(([^,]+),[^)]\)/i 
result = orig.sub(regex){$1} # result => somestuff = VSK1_DVal.RuntimeSUM,

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453233

You say in your question

I use MSSQL 2k5, i think it uses general set of RegEx rules.

This is not true unless you enable CLR and compile and install an assembly. You can use its native pattern matching syntax and LIKE for this as below.

WITH T(C) AS
(

SELECT 'IsNull(VSK1_DVal.RuntimeSUM,0),' UNION ALL
SELECT 'IsNull(VSK1_DVal.RuntimeSUM,123465),'  UNION ALL
SELECT 'No Match'
)
SELECT SUBSTRING(C,8,1+LEN(C)-8-CHARINDEX(',',REVERSE(C),2))
FROM T
WHERE C LIKE 'IsNull(%,_%),'

Upvotes: 0

nycdan
nycdan

Reputation: 2839

Here's a very useful site: http://www.regexlib.com/RETester.aspx

They have a tester and a cheatsheet that are very useful for quick testing of this sort.

I tested the solution by Dave and it works fine except it also removes the trailing comma you wanted retained. Minor thing to fix.

Try this: 

IsNULL\((.*,)0\)

Upvotes: 0

David Mårtensson
David Mårtensson

Reputation: 7600

/IsNULL\((A-Z0-9+),0\)/

Then pick group match number 1.

Upvotes: 0

Related Questions