Professional Web Applications Themes

algorithm for sql gurus - Microsoft SQL / MS SQL Server

(untested) UPDATE phone_numbers SET dialled_prefix = (SELECT TOP 1 prefix FROM PrefixTBL WHERE phone_numbers.dialled_number LIKE prefix + '%' ORDER BY LEN (prefix ) DESC -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "Con" <com> wrote in message news:0c1d01c35c24$eef900e0$gbl...  > dialled_prefix  > example is dialled_number Dialled_prefix  > column, ex.  > dialled_prefix to the longest matching prefix in the > second table so 0044171213512 should have a prefix of > 0044171, not 0044 cause 0044171 is obviosuly longer > > it ain't easy[/ref]...

  1. #1

    Default Re: algorithm for sql gurus

    (untested)

    UPDATE phone_numbers
    SET dialled_prefix = (SELECT TOP 1 prefix
    FROM PrefixTBL
    WHERE phone_numbers.dialled_number LIKE prefix + '%'
    ORDER BY LEN (prefix ) DESC

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Con" <com> wrote in message
    news:0c1d01c35c24$eef900e0$gbl... 
    > dialled_prefix 
    > example is dialled_number Dialled_prefix 
    > column, ex. 
    > dialled_prefix to the longest matching prefix in the
    > second table so 0044171213512 should have a prefix of
    > 0044171, not 0044 cause 0044171 is obviosuly longer
    >
    > it ain't easy[/ref]


    Jacco Guest

  2. #2

    Default Re: algorithm for sql gurus

    Do:

    UPDATE tbl
    SET PreFix = (SELECT TOP 1 p1.col
    FROM PrefixTBL p1
    WHERE PATINDEX(p1.col + '%', tbl.Nbr) > 0
    ORDER BY LEN(p1.col) DESC)
    WHERE PreFix IS NULL ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: algorithm for sql gurus

    UPDATE DialledNumbers
    SET dialled_prefix =
    (SELECT prefix
    FROM Prefixes
    WHERE LEN(prefix)=
    (SELECT MAX(LEN(prefix))
    FROM Prefixes
    WHERE DialledNumbers.dialled_number LIKE prefix+'%')
    AND DialledNumbers.dialled_number LIKE prefix+'%')

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: algorithm for sql gurus

    Con,

    You're right that this ain't easy at all. If I remember correctly,
    The fastest practical solution I ever found was basically this

    declare preflen int
    set preflen = (
    select max(len(Prefix)) from PrefixTBL
    )

    while preflen > 0 begin
    update CallTable set
    Dialled_number = Prefix
    where Dialled_number >= Prefix
    and Dialled_number < Prefix + 'A'
    and Dialled_prefix is NULL
    set preflen = preflen - 1
    end

    It could help to store len(Prefix) as a column
    and index the prefix table on (prefixLen, prefix).
    I also wondered often if a cursor would be faster.

    Once I created a temporary table with the numbers
    I had to identify and additional columns for their
    first 1, 2, 3, 4, 5, 6, and 7 digits. That actually worked
    well, since I could do something like a COALESCE(
    Pref7digs.Prefix,Pref6digs.Prefix,...)
    from Numbers left outer join <7-digit prefixes> Pref7digs
    on NumberLeft7 = Pref7digs.Prefix
    left outer join <6-digit prefixes>
    on NumberLeft6 = Pref6digs.Prefix
    ....

    I think I gave that up because it took too long to materialize
    and index the additional columns - but it's worth thinking
    about.

    Another solution that can be faster is to create a
    comprehensive range table, but this is hard to do
    from a prefix table: For example, with 0044 as GBR
    and 00441 as GBRCELL and 004410 as GBRINFO,
    you end up with

    00440000 - 00440999 GBR
    00441000 - 00441099 GBRINFO
    00441100 - 00441999 GBRCELL
    00442000 - 00449999 GBR

    I never found it worth the effort to try to get this right
    except once for Mexico only.

    As soon as I came close, someone came up with a mess of
    9-digit prefixes for wireless phones on some army base in
    Osaka that made the table double in size.

    If I find some old stuff about this, I'll post again. Good luck,
    and let us know what you figure out.

    -- Steve Kass
    -- Drew University
    -- Ref: A4EBAC21-5977-4EBF-84DA-FD2190D45277

    Con wrote:
     
    >dialled_prefix
    >

    >example is dialled_number Dialled_prefix
    >

    >column, ex.
    >

    >dialled_prefix to the longest matching prefix in the
    >second table so 0044171213512 should have a prefix of
    >0044171, not 0044 cause 0044171 is obviosuly longer
    >
    >it ain't easy
    >
    >[/ref]

    Steve Guest

  5. #5

    Default Re: algorithm for sql gurus

    Hi, this works but not for all dialled numbers
    ex
    dialled number 0082564526
    in the prefix table I have 0082 and 0082123

    so the b_pfx should be updated to 0082, it doesn't do it
     
    >> dialled_prefix 
    >> example is dialled_number Dialled_prefix 
    >> column, ex. 
    >> dialled_prefix to the longest matching prefix in the
    >> second table so 0044171213512 should have a prefix of
    >> 0044171, not 0044 cause 0044171 is obviosuly longer
    >>
    >> it ain't easy[/ref]
    >
    >
    >.
    >[/ref]
    Con Guest

  6. #6

    Default Re: algorithm for sql gurus

    No, it will work fine. Either you have different data that you mentioned or
    you are using a wrong UPDATE statement.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  7. #7

    Default Re: algorithm for sql gurus

    Hi Con,

    I have no problems with the data you provided, see the script at the end of
    this post. Maybe the data in your tables have leading or trailing spaces?
    You can remove them with LTRIM and RTRIM.


    CREATE TABLE phone_numbers (dialled_number varchar(20) NOT NULL,
    Dialled_prefix varchar(20) NULL)
    CREATE TABLE PrefixTBL (prefix varchar(20) NOT NULL)
    GO
    INSERT INTO PrefixTBL (prefix) VALUES('0044')
    INSERT INTO PrefixTBL (prefix) VALUES('0044171')
    INSERT INTO PrefixTBL (prefix) VALUES('0032')
    INSERT INTO PrefixTBL (prefix) VALUES('0082')
    INSERT INTO PrefixTBL (prefix) VALUES('0082123')

    INSERT INTO phone_numbers (dialled_number) VALUES('0044171213512')
    INSERT INTO phone_numbers (dialled_number) VALUES('0044254355545')
    INSERT INTO phone_numbers (dialled_number) VALUES('00325465465')
    INSERT INTO phone_numbers (dialled_number) VALUES('003244444444')
    INSERT INTO phone_numbers (dialled_number) VALUES('0082564526')

    UPDATE phone_numbers
    SET dialled_prefix = (SELECT TOP 1 prefix
    FROM PrefixTBL
    WHERE phone_numbers.dialled_number LIKE prefix + '%'
    ORDER BY LEN (prefix ) DESC)

    SELECT dialled_number, Dialled_prefix FROM phone_numbers

    DROP TABLE phone_numbers, PrefixTBL

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Con" <com> wrote in message
    news:0ce401c35c2d$0a40d2d0$gbl... 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Jacco Guest

  8. #8

    Default Re: algorithm for sql gurus

    Have you considered using the ITU Standard for International telephone
    numbers? The thing is only 15 digits long, well-defined and has
    software available for it. But to answer the immediate question:

    CREATE TABLE Phones
    (phone_nbr VARCHAR(20) NOT NULL PRIMARY KEY,
    prefix VARCHAR(20));

    CREATE TABLE Prefixes
    (prefix VARCHAR(20) NOT NULL PRIMARY KEY);

    INSERT INTO Prefixes VALUES ('0044');
    INSERT INTO Prefixes VALUES ('0044171');
    INSERT INTO Prefixes VALUES ('0032');
    INSERT INTO Prefixes VALUES ('0082');
    INSERT INTO Prefixes VALUES ('0082123');

    INSERT INTO Phones VALUES ('0044171213512', NULL);
    INSERT INTO Phones VALUES ('0044254355545', NULL);
    INSERT INTO Phones VALUES ('00325465465', NULL);
    INSERT INTO Phones VALUES ('003244444444', NULL);
    INSERT INTO Phones VALUES ('0082564526', NULL);

    SELECT LEN(X1.prefix), X1.prefix, P1.phone_nbr
    FROM Prefixes AS X1, Phones AS P1
    WHERE CHARINDEX(X1.prefix, P1.phone_nbr) = 1
    AND NOT EXISTS
    (SELECT *
    FROM Prefixes AS X2
    WHERE CHARINDEX(X2.prefix, P1.phone_nbr) = 1
    AND LEN(X2.prefix) > LEN(X1.prefix));

    The CHARINDEX() says this is a prefix and not an embedded string. The
    length subquery says this is the longest one. The PRIMARY KEYs keep out
    duplicates. Now use this in an UPDATE statement.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

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

  9. #9

    Default Re: algorithm for sql gurus

    Joe,

    Telecommunications service providers provide their own lists
    of prefixes for rating purposes. What's officially <Turkey><Istanbul-Asia>
    might contain many different provider regions and prices. Caller-pays
    services like wireless (in most countries), teletext, and other
    non-geographic
    considerations add to the mess. ITU software isn't going to be much help
    for someone generating bills, verifying charges, or maintaining a least-cost
    routing table when many carriers with many views of the worldwide
    telephone system are involved.

    My experience was that it was surprisingly difficult to solve the problem
    efficiently even for just a few thousand prefixes and a few million phone
    numbers.

    SK

    Joe Celko wrote:
     

    Steve Guest

Similar Threads

  1. /ID tag algorithm
    By Allan_Mills@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: May 20th, 02:10 AM
  2. Algorithm::Huffman
    By Susan in forum PERL Modules
    Replies: 0
    Last Post: December 12th, 01:44 AM
  3. algorithm help....
    By point in forum PHP Development
    Replies: 18
    Last Post: September 11th, 01:46 PM
  4. Search Algorithm
    By Fletcher in forum UNIX Programming
    Replies: 7
    Last Post: August 16th, 03:09 AM
  5. Algorithm problem--Help
    By Alan in forum ASP Database
    Replies: 1
    Last Post: August 15th, 11:32 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