Numeric Order (VB.NET) RSS

• • Reply
  

  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:

  100.1.2.3
  255.1.2.3
  41.1.2.3

  But would like them ordered like so:

  41.1.2.3
  100.1.2.3
  255.1.2.3
  

  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
  
  

  Please click 'Mark as Answer' if my reply helped
  Homepage | YouTube Channel | Post code, not links
  • Edited by crouchie2004 on Nov 16, 2012 09:28 PM
  • Edited by crouchie2004 on Nov 16, 2012 09:34 PM
  • Edited by crouchie2004 on Nov 16, 2012 09:36 PM
• • Reply
  

  sandeepmitta...

  Contributor

  6957 Points

  1082 Posts

  Re: Numeric Order (VB.NET)

  Nov 17, 2012 04:04 AM|LINK

  First create function

  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

  
  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

  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)

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Nov 17, 2012 04:05 AM
• • Reply
  

  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?

  Please click 'Mark as Answer' if my reply helped
  Homepage | YouTube Channel | Post code, not links
• • Reply
  

  Chen Yu - MS...

  All-Star

  21829 Points

  2513 Posts

  Microsoft

  Re: Numeric Order (VB.NET)

  Nov 21, 2012 04:17 AM|LINK

  crouchie2004

  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
  
  Develop and promote your apps in Windows Store
  
  
• • Reply
  

  crouchie2004

  Member

  498 Points

  308 Posts

  Re: Numeric Order (VB.NET)

  Nov 21, 2012 07:23 AM|LINK

  Hi,

  They are being returned in

  <p id="SomeName" runat="server" />

  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?

  Please click 'Mark as Answer' if my reply helped
  Homepage | YouTube Channel | Post code, not links
  • Edited by crouchie2004 on Dec 04, 2012 01:29 AM