Professional Web Applications Themes

REPLACE with regular expressions? - Microsoft SQL / MS SQL Server

You would need to filter this in the WHERE clause of the UPDATE statement. Ex: update <table> set <companycolumn> = REPLACE(<companycolumn>, 'Co', 'Co.') where <companycolumn> ... Depending on the nature of the replace, your WHERE clause could vary. If 'Co' would always be the last characters in the column, then the WHERE clause could be : WHERE RIGHT(RTRIM(<companycolumn>),2) = 'Co' --Drew "Nicolas Verhaeghe" <nospam_nicver_nospam> wrote in message news:6r1Ma.22089$Cw3.965news.randori.com... > I would like to replace the word "Co" in Company field with "Co." > > How do I make sure that the other "co" strings will not be replaced by "co." ...

  1. #1

    Default Re: REPLACE with regular expressions?

    You would need to filter this in the WHERE clause of the UPDATE statement.

    Ex:
    update <table>
    set <companycolumn> = REPLACE(<companycolumn>, 'Co', 'Co.')
    where <companycolumn> ...

    Depending on the nature of the replace, your WHERE clause could vary. If
    'Co' would always be the last characters in the column, then the WHERE
    clause could be : WHERE RIGHT(RTRIM(<companycolumn>),2) = 'Co'

    --Drew

    "Nicolas Verhaeghe" <nospam_nicver_nospam> wrote in message
    news:6r1Ma.22089$Cw3.965news.randori.com...
    > I would like to replace the word "Co" in Company field with "Co."
    >
    > How do I make sure that the other "co" strings will not be replaced by
    "co."
    > as in "Coachcrafters Co" becoming "Co.achcrafters Co."?
    >
    > Can the REPLACE T-SQL function be used with regular expressions?
    >
    > Thanks in advance
    >
    >

    Drew Seale Guest

  2. #2

    Default Re: REPLACE with regular expressions?

    Nicolas,

    You'll have better luck using STUFF than REPLACE for this.
    In order to make sure the "other" co strings won't be replaced,
    you need a rule that allows you to decide whether one should or
    not. If the rule is

    Replace "co" with "co." if
    1) "co" is preceded by a space or is at the start of the string
    and
    2) "co is followed by a space or non-period puncutation, or is at the
    end of the string

    then this sequence might at least come close:

    update myTable set
    Company = stuff(Company,3,1,'.')
    where Company like 'Co[^A-Za-z.-]%'

    update myTable set
    Company = stuff(Company,
    patindex(Company,'%[ ,&]Co[^A-Za-z-]%'+3,1,'.')

    -- need to repeat if there's more than one to replace in the string

    General regular expressions aren't supported beyond what PATINDEX
    and LIKE support.

    Steve Kass
    Drew University

    Nicolas Verhaeghe wrote:
    >I would like to replace the word "Co" in Company field with "Co."
    >
    >How do I make sure that the other "co" strings will not be replaced by "co."
    >as in "Coachcrafters Co" becoming "Co.achcrafters Co."?
    >
    >Can the REPLACE T-SQL function be used with regular expressions?
    >
    >Thanks in advance
    >
    >
    >
    >
    Steve Kass Guest

  3. #3

    Default Re: REPLACE with regular expressions?

    Thanks, Steve.

    So STUFF is a regexp-capable version of REPLACE, if I am right?
    > Nicolas,
    >
    > You'll have better luck using STUFF than REPLACE for this.
    > In order to make sure the "other" co strings won't be replaced,
    > you need a rule that allows you to decide whether one should or
    > not. If the rule is
    >
    > Replace "co" with "co." if
    > 1) "co" is preceded by a space or is at the start of the string
    > and
    > 2) "co is followed by a space or non-period puncutation, or is at the
    > end of the string
    >
    > then this sequence might at least come close:
    >
    > update myTable set
    > Company = stuff(Company,3,1,'.')
    > where Company like 'Co[^A-Za-z.-]%'
    >
    > update myTable set
    > Company = stuff(Company,
    > patindex(Company,'%[ ,&]Co[^A-Za-z-]%'+3,1,'.')
    >
    > -- need to repeat if there's more than one to replace in the string
    >
    > General regular expressions aren't supported beyond what PATINDEX
    > and LIKE support.
    >
    > Steve Kass
    > Drew University
    >
    > Nicolas Verhaeghe wrote:
    >
    > >I would like to replace the word "Co" in Company field with "Co."
    > >
    > >How do I make sure that the other "co" strings will not be replaced by
    "co."
    > >as in "Coachcrafters Co" becoming "Co.achcrafters Co."?
    > >
    > >Can the REPLACE T-SQL function be used with regular expressions?
    > >
    > >Thanks in advance
    > >
    > >
    > >
    > >
    >

    Nicolas Verhaeghe Guest

Similar Threads

  1. MX 2004 - Find&Replace -> Regular expressions?
    By PJ in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 7th, 06:58 PM
  2. Regular Expressions :(
    By Liuk in forum PHP Development
    Replies: 8
    Last Post: January 8th, 09:27 PM
  3. Regular expressions
    By jcwaters in forum PERL Modules
    Replies: 15
    Last Post: December 17th, 04:23 PM
  4. PHP regular expressions
    By Albert in forum PHP Development
    Replies: 9
    Last Post: October 30th, 09:32 PM
  5. Regular Expressions....HELP!
    By Stephajn Craig in forum ASP.NET General
    Replies: 1
    Last Post: July 16th, 06:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139