November 18, 2012 at 10:11 pm
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.
November 18, 2012 at 10:24 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 18, 2012 at 10:41 pm
Thanks Dwain.
I tried
select LEFT(127.0.0.1,len(127.0.0.1-charindex('.',reverse(([Ip]))
from [dbo].[TestTable]
but am getting
Incorrect syntax near the keyword 'from'.
127.0.0.1 is column name.
Since I'll probably have a list of over 100k I don't think the other options would be practical.
November 18, 2012 at 10:47 pm
VegasL (11/18/2012)
Thanks Dwain.I tried
select LEFT(127.0.0.1,len(127.0.0.1-charindex('.',reverse(([Ip]))
from [dbo].[TestTable]
but am getting
Incorrect syntax near the keyword 'from'.
127.0.0.1 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(127.0.0.1, LEN(127.0.0.1)-CHARINDEX('.', REVERSE(127.0.0.1)))
FROM [dbo].[TestTable]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 18, 2012 at 10:47 pm
( problem in your query
select
LEFT(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip]))))
from tablename
November 18, 2012 at 11:18 pm
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(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip]))))
from tablename
group by 127.0.0.1
having count (127.0.0.1)>1
order by (127.0.0.1) desc
It does display the 127.0.0.1 's used more than once but it is also displaying some that are only used once. Any thoughts?
November 18, 2012 at 11:37 pm
your query seems ok and working fine
may be you are selecting LEFT(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip])))) which may leads you to confusion
you can try putting ip in select and find where's the problem
select LEFT(127.0.0.1,len(127.0.0.1)-charindex('.',reverse(([Ip])))),ip
from @t
group by 127.0.0.1
having count (127.0.0.1)>1
order by (127.0.0.1) desc
or else post some sample data in which you are facing problem.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply