What is the most appropriate data type for storing an IP address in SQL server? [duplicate]

by

Storing an IPv4 address as a binary(4) is truest to what it represents, and allows for easy subnet mask-style querying. However, it requires conversion in and out if you are actually after a text representation. In that case, you may prefer a string format.

A little-used SQL Server function that might help if you are storing as a string is PARSENAME, by the way. Not designed for IP addresses but perfectly suited to them. The call below will return ’14’:

SELECT PARSENAME('123.234.23.14', 1)

(numbering is right to left).

More Related Contents:

  1. Difference between numeric, float and decimal in SQL Server
  2. What is SYSNAME data type in SQL Server?
  3. SQL Server equivalent to MySQL enum data type?
  4. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  5. How can I do an UPDATE statement with JOIN in SQL Server?
  6. Why does NULL = NULL evaluate to false in SQL server
  7. The multi-part identifier could not be bound
  8. Subquery using Exists 1 or Exists *
  9. How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
  10. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  11. SQL – How to store and navigate hierarchies?
  12. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  13. Update records in table from CTE
  14. Fastest way to update 120 Million records
  15. how can I Update top 100 records in sql server
  16. How to insert multiple records and get the identity value?
  17. How can a LEFT OUTER JOIN return more records than exist in the left table?
  18. Count Number of Consecutive Occurrence of values in Table
  19. How to replace (null) values with 0 output in PIVOT
  20. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  21. How can I compare time in SQL Server?
  22. SQL – Difference between COALESCE and ISNULL? [duplicate]
  23. SQL: capitalize first letter only [duplicate]
  24. UPDATE and REPLACE part of a string
  25. Improve SQL Server query performance on large tables
  26. T-SQL Skip Take Stored Procedure
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Connecting to Oracle Database using Sql Server Integration Services
  30. Executing SQL query on multiple databases

Leave a Comment