Insert 4 tinyint values into varbinary(4)

Question

I'm working on a function to convert the string representation of an IP into its varbinary(4/16) form.

Right now, I have this:

@stringIP = '192.168.0.3'
select 
 CAST(PARSENAME(@stringIP, 4) as tinyint),
 CAST(PARSENAME(@stringIP, 3) as tinyint),
 CAST(PARSENAME(@stringIP, 2) as tinyint),
 CAST(PARSENAME(@stringIP, 1) as tinyint)

This breaks up my IPv4 Address very well; but I need to figure out how to insert it into a varbinary(4) so I can insert the data to my table. I cannot find ANY TSQL syntax to combine these four tinyints into a varbinary(4) -- I can only find C#/VB examples using paramterized SQL. This is fine and good, but I'm trying to create an SQL Function do use in an INSTEAD OF INSERT trigger.

• Nate: I am open to other/better ways to parse the IP as well, but I've found a few examples using PARSENAME for IPv4 and it seems to work well...
• GSerg: Possible duplicate of stackoverflow.com/questions/695568/…

Answer 1

@stringIP = '192.168.0.3'
select 
 cast(CAST(PARSENAME(@stringIP, 4) as tinyint) as varbinary(1)) +
 cast(CAST(PARSENAME(@stringIP, 3) as tinyint) as varbinary(1)) +
 cast(CAST(PARSENAME(@stringIP, 2) as tinyint) as varbinary(1)) +
 cast(CAST(PARSENAME(@stringIP, 1) as tinyint) as varbinary(1))