Substring with a variable first position
-
Hello,
I wonder if anyone can help me. I need to write a query which gets the 2nd octet of an IP address, but the first octet can be of varying length. For example, it may be 11.200.12.13 or it could be 120.200.12.13. I need to pick out the 2nd octet and determine whether it is > or < 200. So I figure I need to use something like substr but I do not know the starting position, as it could be 4 or 5.
Any ideas?
Many many thanks in advance.
Paula.
-
DECLARE @demo TABLE (IP varchar(15) NOT NULL)
INSERT INTO @demo (IP) VALUES ('9.200.12.13')
INSERT INTO @demo (IP) VALUES ('11.200.12.13')
INSERT INTO @demo (IP) VALUES ('120.200.12.13')
INSERT INTO @demo (IP) VALUES ('10.0.12.13')
INSERT INTO @demo (IP) VALUES ('10.10.12.13')
INSERT INTO @demo (IP) VALUES ('10.200.12.13')
INSERT INTO @demo (IP) VALUES ('invalid data')
SELECT
SUBSTRING(IP, CHARINDEX('.', IP, 1) + 1, CHARINDEX('.', IP, CHARINDEX('.', IP, 1) + 1) - CHARINDEX('.', IP, 1) - 1)
FROM @Demo
WHERE CHARINDEX('.', IP, 1) > 0
-
Or you could also use a split function and only return the 2nd element.
-
Excellent! Many many thanks!
-
Also you could try this.
SELECT PARSENAME('9.200.12.13', 3) AS 'SecondOctet' UNION ALL
SELECT PARSENAME('11.200.12.13', 3) AS 'SecondOctet' UNION ALL
SELECT PARSENAME('120.200.12.13', 3) AS 'SecondOctet' UNION ALL
SELECT PARSENAME('10.0.12.13', 3) AS 'SecondOctet' UNION ALL
SELECT PARSENAME('10.10.12.13', 3) AS 'SecondOctet' UNION ALL
SELECT PARSENAME('10.200.12.13', 3) AS 'SecondOctet' UNION ALL
SELECT PARSENAME('invalid data', 3) AS 'SecondOctet'
Ram
-
Wow thanx for the tip. It's almost a shame we can only use this with dot separated text, but it's really nice to have that in the bag
. -
Here's a scalar UDF I just whipped up that works like ParseName but with a variable delimiter.
Supporting Objects Required:
Numbers table that all databases should have.
Copy and Pasted from: http://vyaskn.tripod.com/fun_with_numbers_in_t-sql_queries.htm
SET
NOCOUNT ON
GO
IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Numbers'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
)
BEGIN
DROP TABLE dbo.Numbers
END
GO
CREATE TABLE dbo.Numbers
(
Number smallint IDENTITY(1, 1) PRIMARY KEY
)
GO
WHILE 1 = 1
BEGIN
INSERT INTO dbo.Numbers DEFAULT VALUES
IF @@IDENTITY = 8000
BEGIN
BREAK
END
END
GO
Source:
CREATE FUNCTION [dbo].[GetDelimitedStringPart] (@String varchar(7998), @Delimiter char(1) = '.', @PartNumber int = 1) RETURNS varchar(255)
AS
BEGIN
DECLARE @StringParts Table (Seq_Id int identity(1,1), StringPart varchar(255))
INSERT INTO @StringParts (StringPart)
SELECT substring(@Delimiter + @String + @Delimiter, Number + 1,charindex(@Delimiter, @Delimiter + @String + @Delimiter, Number + 1) - Number - 1) AS Value
FROM Numbers
WHERE Number <= len(@Delimiter + @String + @Delimiter) - 1
AND substring(@Delimiter + @String + @Delimiter, Number, 1) = @Delimiter
RETURN (Select StringPart from @StringParts where Seq_Id = @PartNumber)
END
Usage:
select dbo.GetDelimitedStringPart('251m252m253m254','m',2)
-
Just thought about it a little more
You could also use
PARSENAME(REPLACE(STRING,'DELIMITER','.'),POSITION)
-
I thaught about that too but you still can only get 4 positions. That's too limited for my taste
. -
The example above the parsename/replace will work for N positions up to 8000/len(delimiter)
Gareth
-
Lol, that's what I get for talking without testing
. -
Thank you all for this, I have gone with the parsename function, which is perfect for my needs.
And I am only slightly worried that you are all so enthusiastic about this!
-
Can you then explain me why I am getting a null with this query??
SELECT PARSENAME(REPLACE('1,2,3,4,5,6,7,8,9,10',',','.'),5)
-
Dunno, I used this one:
SELECT PARSENAME(ip_addr, 3) AS 'SecondOctet'
Because I know I'll always have 4 parts.
-
Ya that's why I wsa excited about the solution, it was exactly what you needed. But I was reffering to a more global solution where you could need to 95th part of the string. Im that case the parsename function cannot work (unless I missed something).
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply