Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

String Function Help Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 10:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:55 PM
Points: 18, Visits: 68
Hello,

Total Newbie to SQL Server, so pardon any non synchronous sql lingo.

In a table, i have column "IP Address"

which has values of ip address such as

102.89.93.101
100.100.10.1
123.94.9.121

etc

what I want SQL Server to do is return the first 3 values after the second dot.

So it should return

102.89.93
100.100.10
123.94.9

How to make possible?

Thanks in advance for any help.
Post #1386146
Posted Sunday, November 18, 2012 10:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
Many ways. Choose your poison:

DECLARE @T TABLE (IP VARCHAR(30))

INSERT INTO @T
SELECT '102.89.93.101'
UNION ALL SELECT '100.100.10.1'
UNION ALL SELECT '123.94.9.121'

SELECT PARSENAME(IP, 4) + '.' +
PARSENAME(IP, 3) + '.' +
PARSENAME(IP, 2)
FROM @T

SELECT LEFT(IP, LEN(IP)-CHARINDEX('.', REVERSE(IP)))
FROM @T





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1386148
Posted Sunday, November 18, 2012 10:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:55 PM
Points: 18, Visits: 68
Thanks Dwain.

I tried

select LEFT([IP],len([IP]-charindex('.',reverse(([Ip]))
from [dbo].[TestTable]

but am getting

Incorrect syntax near the keyword 'from'.


[IP] is column name.

Since I'll probably have a list of over 100k I don't think the other options would be practical.

Post #1386150
Posted Sunday, November 18, 2012 10:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
VegasL (11/18/2012)
Thanks Dwain.

I tried

select LEFT([IP],len([IP]-charindex('.',reverse(([Ip]))
from [dbo].[TestTable]

but am getting

Incorrect syntax near the keyword 'from'.


[IP] is column name.

Since I'll probably have a list of over 100k I don't think the other options would be practical.



You need to make sure your parentheses are properly matched:

SELECT LEFT([IP], LEN([IP])-CHARINDEX('.', REVERSE([IP])))
FROM [dbo].[TestTable]





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1386151
Posted Sunday, November 18, 2012 10:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
( problem in your query

select 
LEFT([IP],len([IP])-charindex('.',reverse(([Ip]))))
from tablename

Post #1386152
Posted Sunday, November 18, 2012 11:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:55 PM
Points: 18, Visits: 68
Thanks for you're help Brian. That worked. I am now trying to show which IP's are used more than once, so I tried


select LEFT([IP],len([IP])-charindex('.',reverse(([Ip]))))
from tablename
group by [ip]
having count ([ip])>1
order by ([ip]) desc

It does display the [ip] 's used more than once but it is also displaying some that are only used once. Any thoughts?
Post #1386159
Posted Sunday, November 18, 2012 11:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
your query seems ok and working fine

may be you are selecting LEFT([IP],len([IP])-charindex('.',reverse(([Ip])))) which may leads you to confusion

you can try putting ip in select and find where's the problem

select LEFT([IP],len([IP])-charindex('.',reverse(([Ip])))),ip
from @t
group by [ip]
having count ([ip])>1
order by ([ip]) desc


or else post some sample data in which you are facing problem.

Post #1386167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse