Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    4

    Pattern Matching

    Hi,

    It would be great if any one of you could provide me help in writing SQL script that identify:

    The rows in a table that has a string that has the following pattern .
    It starts with a charcter followed by 2 digits and the following characters could be alpha-numeric. (eg: U1234A , P34Y98 )

    and this pattern of string should appear more than once. And if there is a AND between these two strings that should be replaced with _ underscore.
    Eg: (U1234A AND P34Y98 ) then it should be replaced as (U1234A _ P34Y98 )



    SQL SERVER 2000 is used.

    Regards
    Bindhu

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    These examples should help

    Starts with a character
    Code:
    WHERE  some_field LIKE '[A-Z]%'
    Ends with a number
    Code:
    WHERE  some_field LIKE '%[0-9]'
    Replace pattern string
    Code:
    SELECT Replace('ABCDEFGHIJ', 'ABC', 'ZZZZZZ')
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2008
    Posts
    4

    Pattern Matching

    Hi,

    Thanks for the quick response.
    I am new to SQL scripting.

    The Table contents are like this.
    ID Name
    1 U209A MAY SET OTHER MODULE/DTCS. OR B1453
    2 U209A AND B1453
    3 U209A AND U2099 AND U209D
    4 U209A OTHER MODULES WILL SET OTHER DTC WITH THIS FAULT. AND OTHER MODULES


    U209A and B1453 are the kind of patterns that I am talking about.
    So I have to write a Script such that it replaces AND with underscore in 2 and 3 only.

    We can find a AND in 4 also but that has not be replaced.

    Any solutions.

    Regards
    Bindhu M

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Try
    Code:
    SELECT *
    FROM   your_table
    WHERE  your_field LIKE '%[A-Z][0-9][0-9][0-9A-Z][0-9A-Z] AND [A-Z][0-9][0-9][0-9A-Z][0-9A-Z]%'
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2008
    Posts
    4
    Hi ,

    Thanks a Lot.


    Regards
    Bindhu M

Posting Permissions

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