SQL (prefer MySQL) String Function to Reverse Domain Name

Question

I have a table which contains a list of domain names and a description.

I wish to use a simple function (native function if possible) for reversing the domain name notation for use in a better, more natural sort order (personal opinion).

For example, I want to have the following data converted from left to right:

admin.test.example.com => com.example.test.admin
api.test.example.com => com.example.test.api
cdn.test.example.com => com.example.test.cdn
test.example.com => com.example.test
admin.staging.example.com => com.example.staging.admin
api.staging.example.com => com.example.staging.api
cdn.staging.example.com => com.example.staging.cdn
staging.example.com => com.example.staging

To do this in PHP I could do something simple like:

$rev_domain = implode('.', array_reverse(explode('.', $domain)));

Is this easily possible in SQL? (prefer SQL standards compliance, or MySQL if extensions are required, or both if you happen to know both).

Clearly another option is to create and maintain a new column which contains the reversed DNS name, however I am looking for something which can be used on-the-fly in a query.

Thanks in advance!

Edit: I'm looking for a solution which will work for any count of sub-domain parts, example: example.com and 1.2.3.4.5.6.7.8.9.0.example.com

You would have to write your own function that loops over the input string searching for `.` characters. — eggyal, Nov 12 '12 at 15:06
Do you *really* need to preserve the forward order inside a label? For most purposes `REVERSE` should be enough for your needs. — kmkaplan, Nov 12 '12 at 15:08
Simply reversing the string would not create the natural ordering capability I am hoping for. For example, it would not put "admin" and "api" close together as the first character of the final domain name part would be api(i)+admi(n) and not (a)pi+(a)dmin. — Drew Anderson, Nov 12 '12 at 15:13

kmkaplan · Accepted Answer · 2012-11-12T18:25:18.380

4

Try this:

CREATE FUNCTION reverse_dns (s CHAR(255))
RETURNS CHAR(255) DETERMINISTIC
BEGIN
    DECLARE i, j INT;
    DECLARE r CHAR(255);
    SET j = LOCATE('.', s, 1);
    IF j = 0 THEN
        RETURN s;
    END IF;
    SET r = LEFT(s, j - 1);
    SET i = j + 1;
    LOOP
        SET j = LOCATE('.', s, i);
        IF j = 0 THEN
            SET r = CONCAT(RIGHT(s, LENGTH(s) - i + 1), '.', r);
            RETURN r;
        END IF;
        SET r = CONCAT(SUBSTRING(s, i, j - i), '.', r);
        SET i = j + 1;
    END LOOP;
END;

edited Nov 12 '12 at 18:25

answered Nov 12 '12 at 16:20

kmkaplan

18,655
4
51
65

Thank you - this works well! Hopefully a quick question - how can you modify this for up to 4KB to 8KB length strings? While not common, it is easy for domain names to exceed 255 bytes. – Drew Anderson Nov 12 '12 at 17:29
Actually domain names are restricted tu 255 bytes. See RFC 1034 3.1. Name space specifications and terminology: “the total number of octets that represent a domain name […] is limited to 255.” – kmkaplan Nov 12 '12 at 17:41

score 0 · Answer 2 · answered Nov 12 '12 at 15:44

It is not particularly pretty, but you can do something like this:

select concat(left(substring_index(concat(val, '.'), '.', -2),
                   locate('.', substring_index(concat(val, '.'), '.', -2))
                  ),
              left(substring_index(concat(val, '.'), '.', -3),
                   locate('.', substring_index(concat(val, '.'), '.', -3))
                  ),
              left(substring_index(concat(val, '.'), '.', -4),
                   locate('.', substring_index(concat(val, '.'), '.', -4))
                  ),
              left(substring_index(concat(val, '.'), '.', -5),
                   locate('.', substring_index(concat(val, '.'), '.', -5))-1
                  )
             )

One of MySQL's strengths is that it has good string functions, and substring_index is one of them.

Thank you, unfortunately I cannot see this working with a variable number of sub-parts within the domain - correct me if I am wrong but this specifically handles a 4-part case? I will update the question above to help make it more specific. — Drew Anderson, Nov 12 '12 at 15:49
To handle variable numbers of parts would require more complex logic. — Gordon Linoff, Nov 12 '12 at 15:53

SQL (prefer MySQL) String Function to Reverse Domain Name

2 Answers2