Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: sort IP and find max and min

    Im struggling with

    MIN(CAST(PARSENAME (ipaddress,1) as decimal)) + '&' + MAX(CAST(PARSENAME(ipaddress,1) as decimal)) as LowestAndHigestIP

    get error message "Error converting data type varchar to numeric." i guess that '&' is the issue. i want to be able to sort high and low IP like number not string.

    ipaddress is varchar

    any suggestions how to solve this?

    /thanks
    Last edited by mrpcguy; 11-28-13 at 11:20.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're getting the Min() and Max() of a decimal.

    You are then trying to concatenate these decimals to a string. You can't do this, you'll have to cast to strings across.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    My suggestion would be to get the max and min and then work on the presentation part. So something like

    Code:
    Select Cast(ip.minIP as varchar) + ' & ' + Cast(ip.maxIP as varchar)
      From
          (Select minIP = min(ipAddress), maxIP = max(ipAddress)
             From ipTable) ip

  4. #4
    Join Date
    Mar 2004
    Posts
    162
    of course, thanks for solution.

Posting Permissions

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