Professional Web Applications Themes

Dynamic like clause - Microsoft SQL / MS SQL Server

What you describe is exactly what I need to do. I would have one table of values and another one of rules. Then I would have a third table of values which must match with the first. In the "rules" table there can be any number of entries and each one will result in a level of match. So, for example, then table might look as follows: RULE Value _ 90 which would mean that if the first nine characters match between table one and table three then it is match level 90 so, if table 1 contains: 1234567890 0987654321 ...

  1. #1

    Default Re: Dynamic like clause

    What you describe is exactly what I need to do.

    I would have one table of values and another one of rules.

    Then I would have a third table of values which must match with the first.

    In the "rules" table there can be any number of entries and each one will
    result in a level of match.
    So, for example, then table might look as follows:

    RULE Value

    _ 90

    which would mean that if the first nine characters match between table one
    and table three then it is match level 90

    so, if table 1 contains:

    1234567890
    0987654321

    and table 2 contains

    1234567899
    1234567880

    then the first row of the second table will match with the first of the
    first table.
    No other matches will be found.

    The rules tables can contain any number of rules and one row may match more
    than one other row.

    The only way I can think of is by using substring and a whole load of case
    statements.

    Any other way?

    Jonathan


    "HSalim" <com> wrote in message
    news:phx.gbl... 
    60 [/ref]
    compare 
    > match. 
    > want 
    > is 
    >
    >[/ref]


    Jonathan Guest

  2. #2

    Default Re: Dynamic like clause

    Jonathan,

    Depending on the complexity of your rules, you
    should be able to come up with some kind of
    scheme. I wouldn't hold out hope for it to be
    too efficient, but for example, if you have
    a Rules table, and x means must match, 0 means
    must not match, and _ means doesn't matter:

    select table1.s, table2.s, Rules.Value
    from Table1, Table2, Rules, SeqTable S
    where S.position <= len(Table1.s) or S.position <= len(Table2.s)
    group by table1.s, table2.s, RulesValue, Rules.PrimaryKeyColumn
    having min(
    case when substring(Rules.RULE,S.position,1) = 'x'
    then case when substring(Table1.s,S.position,1) =
    substring(Table2.s,S.position,1)
    then 1 else 0 end
    when substring(Rules.RULE,S.position,1) <> '_' then 1
    else case when substring(Table1.s,S.position,1) =
    substring(Table2.s,S.position,1)
    then 0 else 1 end
    end) = 1

    Sequence is a table (position int primary key) with values 1 to 8000 that
    you will have to create.

    You could also store all the separate relevant character
    positions separately in Rules, and then it would be something like

    select table1.s, table2.s, Rules.Value
    from table1, table2, Rules R1
    where not exists ( -- a broken matching rule
    select * from Rules R2
    where R2.RULEcharacter = 'x'
    and R2.primarykey = R1.primarykey
    and substring(table1.s,R2.RulePOSITION,1)
    <> substring(table2.s,R2.RulePOSITION,1)
    ) and not exists ( -- a broken must not match rule
    .... same idea
    )

    [untested code, but I think the idea is workable]

    -- Steve Kass
    -- Drew University
    -- Ref: 92461074-9746-4C11-90DE-53163D9F2442





    Jonathan Blitz wrote:
     
    >60
    >
    > [/ref]
    >compare
    >

    >>match.
    >>
    >> 
    >>want
    >>
    >> 
    >>is
    >>
    >> 
    >>
    >>[/ref]
    >
    >
    >
    >[/ref]

    Steve Guest

  3. #3

    Default Re: Dynamic like clause

    First, keep in mind that SQL server has comparitively limited text
    manipulation capabilities. it is designed to be a database, not a text
    manipulation engine.

    For example, what you describe is really a pattern match. Check out Regular
    Expressions in VBscript which has a fairly nice implementation of the Unix
    style Regular expressions. You can define a match criteria and even look
    for sub-matches. See below for some additional info from the help file.
    I have used regular expressions and have love/hate feeling for them - they
    are complicated but when really rewarding when you get the result right.

    If this does not meet your needs, do not despair. I have been told by some
    very knowledgeable people that Unix has some really nifty text manipulation
    tools, - grep as I recall is one of them, and that Perl is perhaps the best
    text editor available. I do not have the knowledge to say more. I also
    know that there are windows implementations of these tools - wingrep and
    winperl.

    Good luck.
    HS
    ---------------
    Regular Expression Syntax
    A regular expression is a pattern of text that consists of ordinary
    characters (for example, letters a through z) and special characters, known
    as metacharacters. The pattern describes one or more strings to match when
    searching a body of text. The regular expression serves as a template for
    matching a character pattern to the string being searched.

    Here are some examples of regular expression you might encounter:

    JScript VBScript Matches
    /^\[ \t]*$/ "^\[ \t]*$" Match a blank line.
    /\d{2}-\d{5}/ "\d{2}-\d{5}" Validate an ID number consisting of 2
    digits, a hyphen, and another 5 digits.
    /<(.*)>.*<\/\1>/ "<(.*)>.*<\/\1>" Match an HTML tag.

    The following table contains the complete list of metacharacters and their
    behavior in the context of regular expressions:

    Character Description
    \ Marks the next character as either a special character, a literal, a
    backreference, or an octal escape. For example, 'n' matches the character
    "n". '\n' matches a newline character. The sequence '\\' matches "\" and
    "\(" matches "(".
    ^ Matches the position at the beginning of the input string. If the
    RegExp object's Multiline property is set, ^ also matches the position
    following '\n' or '\r'.
    $ Matches the position at the end of the input string. If the RegExp
    object's Multiline property is set, $ also matches the position preceding
    '\n' or '\r'.
    * Matches the preceding subexpression zero or more times. For example,
    zo* matches "z" and "zoo". * is equivalent to {0,}.
    + Matches the preceding subexpression one or more times. For example,
    'zo+' matches "zo" and "zoo", but not "z". + is equivalent to {1,}.
    ? Matches the preceding subexpression zero or one time. For example,
    "do(es)?" matches the "do" in "do" or "does". ? is equivalent to {0,1}
    {n} n is a nonnegative integer. Matches exactly n times. For example,
    'o{2}' does not match the 'o' in "Bob," but matches the two o's in "food".
    {n,} n is a nonnegative integer. Matches at least n times. For
    example, 'o{2,}' does not match the "o" in "Bob" and matches all the o's in
    "foooood". 'o{1,}' is equivalent to 'o+'. 'o{0,}' is equivalent to 'o*'.
    {n,m} m and n are nonnegative integers, where n <= m. Matches at least
    n and at most m times. For example, "o{1,3}" matches the first three o's in
    "fooooood". 'o{0,1}' is equivalent to 'o?'. Note that you cannot put a space
    between the comma and the numbers.
    ? When this character immediately follows any of the other quantifiers
    (*, +, ?, {n}, {n,}, {n,m}), the matching pattern is non-greedy. A
    non-greedy pattern matches as little of the searched string as possible,
    whereas the default greedy pattern matches as much of the searched string as
    possible. For example, in the string "oooo", 'o+?' matches a single "o",
    while 'o+' matches all 'o's.
    . Matches any single character except "\n". To match any character
    including the '\n', use a pattern such as '[.\n]'.
    (pattern) Matches pattern and captures the match. The captured match
    can be retrieved from the resulting Matches collection, using the SubMatches
    collection in VBScript or the $0.$9 properties in JScript. To match
    parentheses characters ( ), use '\(' or '\)'.
    (?:pattern) Matches pattern but does not capture the match, that is,
    it is a non-capturing match that is not stored for possible later use. This
    is useful for combining parts of a pattern with the "or" character (|). For
    example, 'industr(?:y|ies) is a more economical expression than
    'industry|industries'.
    (?=pattern) Positive lookahead matches the search string at any point
    where a string matching pattern begins. This is a non-capturing match, that
    is, the match is not captured for possible later use. For example 'Windows
    (?=95|98|NT|2000)' matches "Windows" in "Windows 2000" but not "Windows" in
    "Windows 3.1". Lookaheads do not consume characters, that is, after a match
    occurs, the search for the next match begins immediately following the last
    match, not after the characters that comprised the lookahead.
    (?!pattern) Negative lookahead matches the search string at any point
    where a string not matching pattern begins. This is a non-capturing match,
    that is, the match is not captured for possible later use. For example
    'Windows (?!95|98|NT|2000)' matches "Windows" in "Windows 3.1" but does not
    match "Windows" in "Windows 2000". Lookaheads do not consume characters,
    that is, after a match occurs, the search for the next match begins
    immediately following the last match, not after the characters that
    comprised the lookahead.
    x|y Matches either x or y. For example, 'z|food' matches "z" or
    "food". '(z|f)ood' matches "zood" or "food".
    [xyz] A character set. Matches any one of the enclosed characters. For
    example, '[abc]' matches the 'a' in "plain".
    [^xyz] A negative character set. Matches any character not enclosed.
    For example, '[^abc]' matches the 'p' in "plain".
    [a-z] A range of characters. Matches any character in the specified
    range. For example, '[a-z]' matches any lowercase alphabetic character in
    the range 'a' through 'z'.
    [^a-z] A negative range characters. Matches any character not in the
    specified range. For example, '[^a-z]' matches any character not in the
    range 'a' through 'z'.
    \b Matches a word boundary, that is, the position between a word and a
    space. For example, 'er\b' matches the 'er' in "never" but not the 'er' in
    "verb".
    \B Matches a nonword boundary. 'er\B' matches the 'er' in "verb" but
    not the 'er' in "never".
    \cx Matches the control character indicated by x. For example, \cM
    matches a Control-M or carriage return character. The value of x must be in
    the range of A-Z or a-z. If not, c is assumed to be a literal 'c' character.
    \d Matches a digit character. Equivalent to [0-9].
    \D Matches a nondigit character. Equivalent to [^0-9].
    \f Matches a form-feed character. Equivalent to \x0c and \cL.
    \n Matches a newline character. Equivalent to \x0a and \cJ.
    \r Matches a carriage return character. Equivalent to \x0d and \cM.
    \s Matches any whitespace character including space, tab, form-feed,
    etc. Equivalent to [ \f\n\r\t\v].
    \S Matches any non-whitespace character. Equivalent to [^ \f\n\r\t\v].
    \t Matches a tab character. Equivalent to \x09 and \cI.
    \v Matches a vertical tab character. Equivalent to \x0b and \cK.
    \w Matches any word character including underscore. Equivalent to
    '[A-Za-z0-9_]'.
    \W Matches any nonword character. Equivalent to '[^A-Za-z0-9_]'.
    \xn Matches n, where n is a hexadecimal escape value. Hexadecimal
    escape values must be exactly two digits long. For example, '\x41' matches
    "A". '\x041' is equivalent to '\x04' & "1". Allows ASCII codes to be used in
    regular expressions.
    \num Matches num, where num is a positive integer. A reference back to
    captured matches. For example, '(.)\1' matches two consecutive identical
    characters.
    \n Identifies either an octal escape value or a backreference. If \n
    is preceded by at least n captured subexpressions, n is a backreference.
    Otherwise, n is an octal escape value if n is an octal digit (0-7).
    \nm Identifies either an octal escape value or a backreference. If \nm
    is preceded by at least nm captured subexpressions, nm is a backreference.
    If \nm is preceded by at least n captures, n is a backreference followed by
    literal m. If neither of the preceding conditions exist, \nm matches octal
    escape value nm when n and m are octal digits (0-7).
    \nml Matches octal escape value nml when n is an octal digit (0-3) and
    m and l are octal digits (0-7).
    \un Matches n, where n is a Unicode character expressed as four
    hexadecimal digits. For example, \u00A9 matches the copyright symbol (©).







    "Jonathan Blitz" <co.il> wrote in message
    news:#phx.gbl... 
    more 
    > 60 [/ref]
    a [/ref][/ref]
    the [/ref]
    > compare 
    > > match. 
    > > want [/ref][/ref]
    I [/ref][/ref]
    value [/ref][/ref]
    on. 
    > >
    > >[/ref]
    >
    >[/ref]


    HSalim Guest

  4. #4

    Default Re: Dynamic like clause

    Jonathan
    Here is a good reference and introduction to Regular Expressions.
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/ht
    ml/js56reconusesforregularexpressions.asp


    "Jonathan Blitz" <co.il> wrote in message
    news:#phx.gbl... 
    more 
    > 60 [/ref]
    a [/ref][/ref]
    the [/ref]
    > compare 
    > > match. 
    > > want [/ref][/ref]
    I [/ref][/ref]
    value [/ref][/ref]
    on. 
    > >
    > >[/ref]
    >
    >[/ref]


    HSalim Guest

  5. #5

    Default Re: Dynamic like clause

    A possible thought ...

    Is there any way I can create my own scalar functions in SQL Server?
    If so then my life would be much easier.

    Jonathan

    "Jonathan Blitz" <co.il> wrote in message
    news:#phx.gbl... 
    more 
    > 60 [/ref]
    a [/ref][/ref]
    the [/ref]
    > compare 
    > > match. 
    > > want [/ref][/ref]
    I [/ref][/ref]
    value [/ref][/ref]
    on. 
    > >
    > >[/ref]
    >
    >[/ref]


    Jonathan Guest

  6. #6

    Default Re: Dynamic like clause

    This will get your "off by one digit" match. you need a table of
    sequential numbers, which is a standard SQL programming trick.

    SELECT DISTINCT N1.nbr
    FROM Sequence AS S1, NumberList AS N1
    WHERE '123456789'
    LIKE SUBSTRING(nbr, 1, seq)
    + '_'
    + SUBSTRING(nbr, (seq + 2), LEN(nbr))
    AND S1.seq BETWEEN 1 AND (LEN(nbr) -1)

    CREATE TABLE Numberlist (nbr CHAR(9) NOT NULL PRIMARY KEY);
    INSERT INTO Numberlist VALUES ('123456089');
    INSERT INTO Numberlist VALUES ('123056789');
    INSERT INTO Numberlist VALUES ('123456780');
    INSERT INTO Numberlist VALUES ('123456789');
    INSERT INTO Numberlist VALUES ('023456789');
    INSERT INTO Numberlist VALUES ('023456780');

    nbr
    ---------
    123056789
    123456089
    123456780
    123456789

    You might want to research check digits and error correcting codes.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  7. #7

    Default Re: Dynamic like clause

    Many thanks.
    The idea of the sequence is what solved my problems.
    This is what I did:

    CREATE TABLE MatchList (pattern CHAR(9) NOT NULL PRIMARY KEY); -- the match
    patterns
    INSERT INTO MatchList VALUES ('_========');
    INSERT INTO MatchList VALUES ('=_=======');
    INSERT INTO MatchList VALUES ('==_======');
    INSERT INTO MatchList VALUES ('===_=====');
    INSERT INTO MatchList VALUES ('====_====');
    INSERT INTO MatchList VALUES ('=====_===');
    INSERT INTO MatchList VALUES ('======_==');
    INSERT INTO MatchList VALUES ('=======_=');
    INSERT INTO MatchList VALUES ('========_');

    CREATE TABLE Target (nbr CHAR(9) NOT NULL PRIMARY KEY); -- The values to be
    found
    INSERT INTO target VALUES ('123456089');
    INSERT INTO target VALUES ('543434344');

    CREATE TABLE Source(nbr CHAR(9) NOT NULL PRIMARY KEY); -- The values to be
    searched
    INSERT INTO Source VALUES ('123456089');
    INSERT INTO Source VALUES ('123056789');
    INSERT INTO Source VALUES ('123456780');
    INSERT INTO Source VALUES ('123456789');
    INSERT INTO Source VALUES ('023456789');
    INSERT INTO Source VALUES ('023456780');

    plus the sequence table with values 1 - 9

    The SQL looks like this:


    SELECT DISTINCT tr1.nbr
    FROM Seque AS Se1, Source AS sr1 ,matchlist ma1, Target tr1
    WHERE
    substring(ma1.pattern,seq,1) = '_'

    AND Se1.seq BETWEEN 1 AND (LEN(tr1.nbr) -1)

    and not exists
    (
    select 1
    FROM Seque AS Se1, Source AS sr2 ,matchlist ma2, Target tr2
    where

    (substring(ma2.pattern,seq,1) = '=' and substring(sr2.nbr,seq,1)
    <> substring(tr2.nbr,seq,1))
    and sr2.nbr = sr1.nbr and tr2.nbr = tr1.nbr and ma2.pattern =
    ma1.pattern
    AND Se1.seq BETWEEN 1 AND (LEN(tr2.nbr) -1)
    )

    the result:

    nbr
    123456089

    Now I just have to see how fast it will be with many thousands of rows. :)

    Many thanks to all that helped!

    Jonathan Blitz
    AnyKey Limited
    Israel



    "Joe Celko" <edu> wrote in message
    news:phx.gbl... 


    Jonathan Guest

  8. #8

    Default Re: Dynamic like clause

    Jonathan Blitz (co.il) writes: 

    Yes, look up CREATE FUNCTION in Books Online.

    This feature was added in SQL2000.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  9. #9

    Default Re: Dynamic like clause

    It appears that I have version 2000 but my doention is for version 7.
    Where can I download more up-to-date doentaion.

    Jonathan

    "Carlos Lee" <com> wrote in message
    news:phx.gbl... 
    > match [/ref]
    to 
    > be [/ref]
    substring(sr2.nbr,seq,1) [/ref]
    :) 
    > >
    > >[/ref]
    >
    >[/ref]


    Jonathan Guest

  10. #10

    Default Re: Dynamic like clause

    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

    Tim S

    "Jonathan Blitz" <co.il> wrote in message
    news:phx.gbl... [/ref]
    dont 
    > > match [/ref]
    > to [/ref][/ref]
    to [/ref]
    > substring(sr2.nbr,seq,1) [/ref][/ref]
    rows. 
    > >
    > >[/ref]
    >
    >[/ref]



    Tim Guest

Similar Threads

  1. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  2. Dynamic "Where" Clause
    By bjr3g in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 30th, 03:48 PM
  3. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  4. if -s clause
    By DBSMITH@OhioHealth.com in forum PERL Beginners
    Replies: 11
    Last Post: June 8th, 03:54 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