How do I add a character ')' at a specific position?

Swarn Singh

Hi Experts

I been asked to find CONVERT(varchar( and replace with RTRIM(CONVERT(varchar( in all of Stored procedures of a SQL database. I generated the script and find replace that I have done by open script in Notepad++ which is easy part.

Code in Stored procedures need to replace looks like this …
CONVERT(varchar(5), col$1) + '~' + CONVERT(varchar(8), col$2) + '~AD~'

Code should looks like after replacement…
RTRIM(CONVERT(varchar(5), col$1)) + '~' + RTRIM(CONVERT(varchar(8), col$2)) + '~AD~'

The difficult part for me is adding extra closing ) brackets due to add RTRIM( with open bracket.
If code is same in all procedures it is very much straight forward but it is not… for example…

convert(varchar(10), dbo.ADDRESS_AD.AD_ALTER_DATE, 103)
rconvert(varchar(10), dbo.ADDRESS_AD.AD_ALTER_DATE, 103)

or

SELECT @ent_status = dbo.ORGANISATION_ORG.ORG_ENTITY_STATUS, @keystring = dbo.ORGANISATION_ORG.ORG_NAME + ', ' + convert(varchar(30), dbo.ORGANISATION_TYPE_CODES_OTC.OTC_DESCRIPTION) + ', ALTERd: ' + convert(varchar(10), dbo.ORGANISATION_ORG.ORG_ALTER_DATE, 103)

So will be great if someone can help with regex or other method to adding closing ) bracket for various diff pattern.
Thanks in Advance.

guy038

Hello, @swarn-singh and All,

I suppose that the key word RCONVERT is not concerned by the replacement. ONLY the expression CONVERT(varchar(..), ..........) must be changed as RTRIM(CONVERT(varchar(..), ..........))

If my assumption is not exact, just tell me about it !

So, given, this sample :

CONVERT(varchar(5), col$1) + '~' + CONVERT(varchar(8), col$2) + '~AD~'

convert(varchar(10), dbo.ADDRESS_AD.AD_ALTER_DATE, 103)
rconvert(varchar(10), dbo.ADDRESS_AD.AD_ALTER_DATE, 103)

SELECT @ent_status = dbo.ORGANISATION_ORG.ORG_ENTITY_STATUS, @keystring = dbo.ORGANISATION_ORG.ORG_NAME + ', ' + convert(varchar(30), dbo.ORGANISATION_TYPE_CODES_OTC.OTC_DESCRIPTION) + ', ALTERd: ' + convert(varchar(10), dbo.ORGANISATION_ORG.ORG_ALTER_DATE, 103)

The following regex S/R :

SEARCH (?i-s)\b(CONVERT$varchar\(\d+$.+?\))

REPLACE RTRIM$$1$

Would output the expected text :

RTRIM(CONVERT(varchar(5), col$1)) + '~' + RTRIM(CONVERT(varchar(8), col$2)) + '~AD~'

RTRIM(convert(varchar(10), dbo.ADDRESS_AD.AD_ALTER_DATE, 103))
rconvert(varchar(10), dbo.ADDRESS_AD.AD_ALTER_DATE, 103)

SELECT @ent_status = dbo.ORGANISATION_ORG.ORG_ENTITY_STATUS, @keystring = dbo.ORGANISATION_ORG.ORG_NAME + ', ' + RTRIM(convert(varchar(30), dbo.ORGANISATION_TYPE_CODES_OTC.OTC_DESCRIPTION)) + ', ALTERd: ' + RTRIM(convert(varchar(10), dbo.ORGANISATION_ORG.ORG_ALTER_DATE, 103))

Of course, select the Regular expression search mode and tick, preferably, the Wrap around option

Then, click, either, several times on the Replace button or once only on the Replace All button

If OK, I could explain how this regex works, next time !

Best Regards,

guy038