CREATE FUNCTION dbo.udf_GetNthOccurrence(@string VARCHAR(MAX), @occurrence_val VARCHAR(MAX), @occurrence_no INT)
RETURNS INT AS
BEGIN
DECLARE @ctr INT, @pos INT, @len INT
SET @ctr = 0
SET @pos = 0
SET @len = LEN(@occurrence_val)
WHILE @ctr<@occurrence_no
BEGIN
SET @pos = CHARINDEX(@occurrence_val, @string, @pos) + @len
IF @pos = @len
BEGIN
RETURN -1
END
SET @ctr = @ctr+1
END
RETURN @pos - @len
END
declare @tab table(IP varchar(100))
insert into @tab
select '100.1.2.3' union all
select '255.1.2.3' union all
select '41.2.2.3' union all
select '41.100.200.30'
SELECT IP
FROM (
SELECT dbo.udf_GetNthOccurrence(IP, '.' , 1) as pos1
, dbo.udf_GetNthOccurrence(IP, '.' , 2) as pos2
, dbo.udf_GetNthOccurrence(IP, '.' , 3) as pos3
, IP
FROM @tab--TableName
WHERE IP IS NOT NULL
) T ORDER BY CAST(LEFT(IP, pos1-1) AS INT)
, CAST(SUBSTRING(IP, pos1+1, pos2-pos1-1) AS INT)
, CAST(SUBSTRING(IP, pos2+1, pos3-pos2-1) AS INT)
, CAST(STUFF(IP, 1, pos3, '') AS INT)
Not being ungrateful but I asked for it in VB.NET not SQL. The customer doesn't want to use it as a stored procedure
Any ideas of how to convert my original post, the code in the link I posted into VB.NET?
Hi,
Which control do you use to display those IP address? Where do you read or store those IP address? Please tell us more information about your issue. Thanks a lot.
Best Regards,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
In my original post, I had a link to the solution in SQL. Would like someone to convert that to a VB.NET 2012 code-behind SQL Query and not a stored procedure. Apparently, at the moment there are around 55, 000 results to sort and return
Any ideas Chen Yu?
Please click 'Mark as Answer' if my reply helped
Homepage | YouTube Channel | Post code, not links
crouchie2004
Member
498 Points
308 Posts
Numeric Order (VB.NET)
Nov 16, 2012 09:24 PM|LINK
Hi,
I have a column which is populated with IP address but doesn't order correctly
Example:
But would like them ordered like so:
At the moment I just do this: 'SELECT DISTINCT ([IP]) FROM TableName WHERE [IP] IS NOT NULL GROUP BY [IP] ORDER BY [IP] ASC' but it doesn't work.
I found this article: http://www.sql-server-helper.com/tips/sort-ip-address.aspx which works in SQL but how to do this in VB.NET code? If the IP address appears in the field twice only to display it once
How do I do it in VB.NET 2012 | ASP 4.5, please?
Thanks in advance
Homepage | YouTube Channel | Post code,
not linkssandeepmitta...
Contributor
6957 Points
1082 Posts
Re: Numeric Order (VB.NET)
Nov 17, 2012 04:04 AM|LINK
First create function
To know more about the function refer this link
http://itdeveloperzone.blogspot.in/2012/03/find-nth-occurrence-of-character-sql.html
Then use this below solution
Sandeep Mittal | My Blog - IT Developer Zone
crouchie2004
Member
498 Points
308 Posts
Re: Numeric Order (VB.NET)
Nov 18, 2012 10:50 PM|LINK
Hi,
Thanks for the reply!
Not being ungrateful but I asked for it in VB.NET not SQL. The customer doesn't want to use it as a stored procedure
Any ideas of how to convert my original post, the code in the link I posted into VB.NET?
Homepage | YouTube Channel | Post code,
not linksChen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: Numeric Order (VB.NET)
Nov 21, 2012 04:17 AM|LINK
Hi,
Which control do you use to display those IP address? Where do you read or store those IP address? Please tell us more information about your issue. Thanks a lot.
Best Regards,
Feedback to us
Develop and promote your apps in Windows Store
crouchie2004
Member
498 Points
308 Posts
Re: Numeric Order (VB.NET)
Nov 21, 2012 07:23 AM|LINK
Hi,
They are being returned in
using semi-colon delimiter
In my original post, I had a link to the solution in SQL. Would like someone to convert that to a VB.NET 2012 code-behind SQL Query and not a stored procedure. Apparently, at the moment there are around 55, 000 results to sort and return
Any ideas Chen Yu?
Homepage | YouTube Channel | Post code,
not links