Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

storing IP addresses in SQL database and sorting

Posted on 2014-03-19
6
Medium Priority
?
628 Views
Last Modified: 2014-03-19
developing an app in lightswitch using data stored in SQL server.    one of the fields is IP address and I need to be able to sort by IP address.  

One suggestion was to split the ip into four columns 3 digits each but that is ugly for entry and reporting.

the other was to enter the IP address with zeros such as 192.168.001.001 again not a good option

the third options was try to create a computed column that coverts the IP address to a sortable number by inserting the zeros or converting binary whatever is necessary

I think this is the best option but I have to idea how to do this in SQL I created a computed column but need assistance with the formula
0
Comment
Question by:Zoldy2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
AshleyBryant earned 2000 total points
ID: 39939567
Your best bet for padding the IP with 0s is to do it at the time of storage rather than using a computed column.  You could either do this in your code at the time that it's saved, make a trigger that does it for you, or just call a scalar function to get the value.  That being said, to answer your original question of how to do this with a computed column, you would make the column invoke a scalar function like the one below:

CREATE FUNCTION dbo.PadIP 
(
	@IP_IN varchar(15)
)
RETURNS varchar(15)
AS
BEGIN
	DECLARE @IP_OUT varchar(15) = ''
	DECLARE @chunk varchar(3) = ''

	WHILE (CHARINDEX('.',@IP_IN) > 0)
	BEGIN
		SELECT @chunk = LTRIM(RTRIM(SUBSTRING(@IP_IN, 1, CHARINDEX('.', @IP_IN) - 1)))
		SELECT @IP_OUT = @IP_OUT + REPLICATE('0', 3 - LEN(@chunk)) + @chunk + '.'
		SET @IP_IN = SUBSTRING(@IP_IN, CHARINDEX('.', @IP_IN) + 1, LEN(@IP_IN))
	END

	SELECT @IP_OUT = @IP_OUT + REPLICATE('0', 3 - LEN(@IP_IN)) + @IP_IN

	RETURN @IP_OUT
END

Open in new window


Run that script on your server.  To test it, try running a query like this:

SELECT dbo.PadIP ('8.12.134.10')

Open in new window


You should get the following output:  008.012.134.010

To make your computed column use the function, if memory serves, you would enter your Formula as dbo.padIP([Name_of_Unpadded_IP_Field]).
0
 
LVL 2

Author Comment

by:Zoldy2000
ID: 39939611
I don't really like modify the actual data.   It makes it messy when reporting and viewing.   I will try your suggestion on the computed column and see if that works.
0
 
LVL 12

Expert Comment

by:AshleyBryant
ID: 39939666
To be a little more clear, I meant that you should store the padded IP address in another column, not replace the existing one.  That way you could produce the padded IP address once and store it to use for sorting.  The padded IP column would never have to be used for display.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 2

Author Comment

by:Zoldy2000
ID: 39939706
sorry I know this is a dumb question but how do i run that script on server?    Does it then get stored somewhere for use?
0
 
LVL 12

Expert Comment

by:AshleyBryant
ID: 39939733
Assuming you are using SQL Server Management Studio:

1. Right-click on your database name.
2. Click on "New Query".
3. Paste the script from above in the query window.
4. Click the "! Execute" button or hit the F5 key.

This will store the function in the database so that it can be used at any time.
0
 
LVL 2

Author Comment

by:Zoldy2000
ID: 39939780
Great work...   that worked perfectly thanks....
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question