SSIS Derived Column, Expression Help

Question

Post reply

SSIS Derived Column, Expression Help

rocky_498

SSCertifiable

Points: 6532
More actions
October 7, 2014 at 8:06 am

#291309

How I can change this IP Address
14.192.128.0
To
014.192.128.000
Or how I can parse above IP in 4 Pieces
14.192.128.0
To
14
192
128
0
in SSIS. I know I can use derived column. I need expression help.
Thank You.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Maybe with this set of formulas and noting that I used the variable @[User::ip] instead of a column:

SUBSTRING( @[User::ip], 1 , FINDSTRING( @[User::ip] , ".", 1 ) - 1)

SUBSTRING( @[User::ip], FINDSTRING( @[User::ip] , ".", 1 ) + 1 , FINDSTRING( @[User::ip] , ".", 2 ) - FINDSTRING( @[User::ip] , ".", 1 ) - 1)

SUBSTRING( @[User::ip], FINDSTRING( @[User::ip] , ".", 2 ) + 1 , FINDSTRING( @[User::ip] , ".", 3 ) - FINDSTRING( @[User::ip] , ".", 2 ) - 1)

SUBSTRING( @[User::ip], FINDSTRING( @[User::ip] , ".", 3 ) + 1 , 3)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Mohit Dhiman Say Hey Kid Points: 677 More actions · Answer 2

If you want four separate parts :

First Part:

SUBSTRING(IP,1,FINDSTRING(IP,".",1) - 1)

Second Part:

SUBSTRING(IP,FINDSTRING(IP,".",1) + 1,FINDSTRING(IP,".",2) - FINDSTRING(IP,".",1) - 1)

Third Part:

SUBSTRING(IP,FINDSTRING(IP,".",2) + 1,FINDSTRING(IP,".",3) - FINDSTRING(IP,".",2) - 1)

Fourth Part:

SUBSTRING(IP,FINDSTRING(IP,".",3) + 1,LEN(IP) - FINDSTRING(IP,".",3))

If you want the whole string in 3 digit placeholder format:

(LEN(SUBSTRING(IP,1,FINDSTRING(IP,".",1) - 1))==1?"00": (LEN(SUBSTRING(IP,1,FINDSTRING(IP,".",1) - 1))==2?"0":""))+SUBSTRING(IP,1,FINDSTRING(IP,".",1) - 1)

+"."+

(LEN(SUBSTRING(IP,FINDSTRING(IP,".",1) + 1,FINDSTRING(IP,".",2) - FINDSTRING(IP,".",1) - 1))==1?"00": (LEN(SUBSTRING(IP,FINDSTRING(IP,".",1) + 1,FINDSTRING(IP,".",2) - FINDSTRING(IP,".",1) - 1))==2?"0":""))+SUBSTRING(IP,FINDSTRING(IP,".",1) + 1,FINDSTRING(IP,".",2) - FINDSTRING(IP,".",1) - 1)

+"."+

(LEN(SUBSTRING(IP,FINDSTRING(IP,".",2) + 1,FINDSTRING(IP,".",3) - FINDSTRING(IP,".",2) - 1))==1?"00": (LEN(SUBSTRING(IP,FINDSTRING(IP,".",2) + 1,FINDSTRING(IP,".",3) - FINDSTRING(IP,".",2) - 1))==2?"0":""))+SUBSTRING(IP,FINDSTRING(IP,".",2) + 1,FINDSTRING(IP,".",3) - FINDSTRING(IP,".",2) - 1)

+"."+

(LEN(SUBSTRING(IP,FINDSTRING(IP,".",3) + 1,LEN(IP) - FINDSTRING(IP,".",3)))==1?"00": (LEN(SUBSTRING(IP,FINDSTRING(IP,".",3) + 1,LEN(IP) - FINDSTRING(IP,".",3)))==2?"0":""))+SUBSTRING(IP,FINDSTRING(IP,".",3) + 1,LEN(IP) - FINDSTRING(IP,".",3))

"The price of anything is the amount of life you exchange for it" - Henry David Thoreau