T-SQL

• Viv123

  SSC Veteran

  Points: 292

  More actions

  March 24, 2013 at 12:41 pm

  #291418

  Hi SQL Gurus,

  I need some help .. I want only the last part of IP from the string.For example :-

  jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn

  i need "123" .

  The ip address will be dynamic/change.

  Any help will be highly appreciated.

  Thanks,

  Viv

  
  Roshan

• Jeff Moden

  SSC Guru

  Points: 1004601

  More actions

  March 24, 2013 at 2:17 pm

  #1600130

  There are a ton of ways to do this but they all usually depend on certain things being there unless you happen to be pretty good at writing AI code.

  Assuming that the only periods in the whole string will be a part of the IP address and the IP address will always be immediately followed by a colon, this is one of the simplest methods.

  --===== Setup the test. This is NOT a part of the solution.

  DECLARE @SomeString VARCHAR(8000);

  SELECT @SomeString = 'jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn';

  --===== Demonstrate the solution which could be modified to work on a table column.

  SELECT SUBSTRING(PARSENAME(@SomeString,1),1,CHARINDEX(':',PARSENAME(@SomeString,1))-1);

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Steven Willis

  SSCrazy Eights

  Points: 9893

  More actions

  March 24, 2013 at 8:16 pm

  #1600156

  I haven't tested any of these options for performance. On a small data set all of them will probably perform OK. But if you are going to use this in production or on a very large table then it would be necessary to create some performance metrics against the real data.

  --some options

  ;WITH sampledata

  AS

  (

  SELECT * FROM

  (VALUES

  ('jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.456:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.789:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.012:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.321:17100?database=us_nnnn')

  ) AS data(127.0.0.1)

  )

  SELECT

  --if the IP part 4 is always 3 digits and no periods in the text following the IP

  SUBSTRING(IP,LEN(IP)+2-CHARINDEX('.',REVERSE(IP)),3) AS [IP4-1]

  --if everything before and after the IP is always the same

  ,STUFF(REVERSE(STUFF(REVERSE(IP),1,23,'')),1,31,'') AS [IP4-2]

  --will find the IP part even if the other text changes assuming just the 3 periods in the IP

  ,STUFF(REVERSE(STUFF(REVERSE(IP),1,CHARINDEX(':',REVERSE(IP)),'')),1,CHARINDEX('.',IP,(CHARINDEX('.',IP,CHARINDEX('.',IP,(CHARINDEX('.',IP)+1)))+1)),'') AS [IP4-3]

  FROM

  sampledata

  --this will work even if the other text is variable.

  --assumes the only periods are in the IP address...if not just change

  --the itemnumber in the where clause (see second example)

  ;WITH sampledata

  AS

  (

  SELECT * FROM

  (VALUES

  ('jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.456:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.789:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.012:17100?database=us_nnnn'),

  ('jdbc:inetpool:inetdae:10.25.81.321:17100?database=us_nnnn')

  ) AS data(127.0.0.1)

  )

  SELECT

  LEFT(Item,3) AS IP4

  FROM

  sampledata s

  CROSS APPLY

  dbo.DelimitedSplit8K(s.IP,'.') dsk

  WHERE

  ItemNumber = 4

  ;WITH sampledata

  AS

  (

  SELECT * FROM

  (VALUES

  ('jdbc.inetpool.inetdae:10.25.81.123:17100?database=us_nnnn'),

  ('jdbc.inetpool.inetdae:10.25.81.456:17100?database=us_nnnn'),

  ('jdbc.inetpool.inetdae:10.25.81.789:17100?database=us_nnnn'),

  ('jdbc.inetpool.inetdae:10.25.81.012:17100?database=us_nnnn'),

  ('jdbc.inetpool.inetdae:10.25.81.321:17100?database=us_nnnn')

  ) AS data(127.0.0.1)

  )

  SELECT

  LEFT(Item,3) AS IP4

  FROM

  sampledata s

  CROSS APPLY

  dbo.DelimitedSplit8K(s.IP,'.') dsk

  WHERE

  ItemNumber = 6