sql server - Compare two string representations of IP address in MS SQL 2012 -

sql server - Compare two string representations of IP address in MS SQL 2012 -

i have next table on ms sql 2012 database server:

create table ip_lookup ( ip_start varchar(16) not null, // 5.200.100.0 ip_end varchar(16) not null, // 5.202.255.255 country char(2) null, primary key clustered (ip_start));

i cannot alter construction of table because updated quite - 1 time per 2-3 days.

i need country specific ip given table above. hence need comparer find right row. see next approaches:

store ip_start , ip_end unsigned int32 , it's quite easy compare them. (not alternative me because can not alter table schema)

split each ip 4 octets (which should integers because strings compared alphabetically , 2.2.10.2 less 2.2.2.2)

is there way compare 2 string ips using ms sql?

if store strings in "zero-padded" format, can accomplish want strings. so, 5.200.100.0 005.200.100.000. direct string comparisons work, , queries take advantage of indexes.

when working ips, have internal format (32-bit or 128-bit) along string representation. internal format needed prevent comparing errors.

if performance not issue, can convert string form binary form or character form. purpose, parsename() can quite useful (see here). when working ips, performance is issue, should way store info more effectively.

sql sql-server ip