String Functions

Question

Post reply

String Functions

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
November 21, 2007 at 10:37 am

#180679

Hello All
I am working with some strings that are stored in the database. Not my most favorite thing. But here is what I have and what I am looking for. I have a table that is storing IP Addresses. I would like to be able to select the second and third octet from the IP Address.
Here is an example: 10.3.5.15, or 10.25.215.10
So I cannot fully use the substring function for this. Since every IP address has a different length. I would like to select the 3, or the 5, or the 25, or the 215 Each IP address does have a period in the string separating the octets.
Thanks in advance
Andrew

Viewing 9 posts - 1 through 8 (of 8 total)

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

EvilPostIT SSCrazy Points: 2578 More actions · Answer 1

declare @ip varchar(15)

select @ip='1.10.20.30'

select substring(@ip,(charindex('.',@ip)+1),(len(@ip)-(CHARINDEX ('.',@ip) + CHARINDEX ('.',REVERSE(@ip)))))

Nuke the site from orbit, its the only way to be sure... :w00t:

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

That is very close. But what I needed was each octet of the IP address in a separate select.

IP Address = 10.5.215.12

like this:

select the second octet AS secondOctet, third octet AS ThirdOctet, fourth octet AS FourthOctet

SecondOctet ThirdOctet FourthOctet

5 215 12

Thank You for what you gave me, I will work with that and see if I can get the query to return the string in this fashion. Sorry if my first post was not clear about that.

Thanks again

Andrew

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

How about ...

DECLARE @ipList TABLE (ipAddress VARCHAR(15))

INSERT @ipList

SELECT '68.251.41.3' UNION SELECT '10.0.5.112' UNION SELECT '192.168.7.1' UNION SELECT '127.0.0.1'

SELECT

PARSENAME(ipAddress,4) AS firstOctet,

PARSENAME(ipAddress,3) AS secondOctet,

PARSENAME(ipAddress,2) AS thirdOctet,

PARSENAME(ipAddress,1) AS fourthOctet

FROM

@ipList

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 4

Jason... that is brilliant! You have no idea how long I spent trying to substring, charindex... what a mess.

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

I learned it from watching these forums.

It's actually the keyword to parse out object names, and just happens to work with IP addresses.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

EvilPostIT SSCrazy Points: 2578 More actions · Answer 6

I completely agree. For example i now know to use parsename instead of CHARINDEX AND SUBSTRING to sort out the octets. 🙂

Nuke the site from orbit, its the only way to be sure... :w00t:

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

Thanks Jason

That did perfectly, just what I needed.

Have a good one

Andrew

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

How about ...

Perfect...

--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)