Querying the IP address - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querying the IP address

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-07-18 : 05:53:23

Hi I am having the IP address in one of my field like

IPAddress
10.10.112.150
10.10.113.153
10.10.114.153
10.10.115.154

I am expecting the query to only get the 1st 3 part of the IP address like

IPAddress
10.10.112
10.10.113
10.10.114
10.10.115

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-18 : 06:10:58

SELECT REVERSE( SUBSTRING(REVERSE(IPAddress), CHARINDEX('.', REVERSE(IPAddress))+1, LEN(IPAddress)))
FROM table

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-18 : 06:18:20

You can also abuse PARSENAME as coincidentaly IPv4 addresses are a 4 part naming convention just like Sql server naming conventions for db's (SERVER.DATABASE.SCHEMA.TABLE)

Example:


DECLARE @ip VARCHAR(16) = '10.10.112.150'
SELECT PARSENAME(@ip,4) + '.' + PARSENAME(@ip,3) + '.' + PARSENAME(@ip,2)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-07-18 : 07:27:21

thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources