Community

    • Login
    • Search
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search

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

    General Discussion
    2
    2
    75
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Swarn Singh
      Swarn Singh last edited by

      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.

      1 Reply Last reply Reply Quote 0
      • guy038
        guy038 last edited by 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

        1 Reply Last reply Reply Quote 2
        • First post
          Last post
        Copyright © 2014 NodeBB Forums | Contributors