Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Unanswered: Change ip adress in a string

    Hello everybody,

    we got data from a dns server that looks like:

    some colums.... |121.12.250.2.somestring......|

    The ip adress should look like 2.250.12.121:

    We could use charindex, substring and len to cut the 4 parts of the ip
    and rebuild it in the right format.
    Starts with
    SELECT
    CHARINDEX('.',mycolumn)
    ,LEFT(mycolumn, CHARINDEX('.',mycolumn) - 1) as part_four_of_ip
    ,CHARINDEX('.', SUBSTRING(mycolumn,CHARINDEX('.',mycolumn) - 1),len(mycolumn)
    as position_of_second_dot
    ........ and so on.....

    This will cause an awful long code.

    Any ideas how to shorten that code?

    best regards

    Michael

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @ip varchar(20) = '1.2.3.124'
    
    SELECT @ip
         , ParseName(@ip, 4)
         , ParseName(@ip, 3)
         , ParseName(@ip, 2)
         , ParseName(@ip, 1)
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    P.S. note what happens when you change the variable value to
    Code:
    DECLARE @ip varchar(20) = '1.2.3.4.5'
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You may want to create a function (perhaps a CLR function, if the number of calls will likely be high) to do this, and simply call that. In the code, you will only see the function call, and it can be used in any number of other places.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •