CODEWITHSUNDEEP
sqlsql-serversql-server-2005
spas2k
spas2k

Reputation: 519

sql split string based on delimiter with varying numbers

How can I split this string 10.159.101.0-10.159.101.255 into two strings?

I can accomplish this in Mysql in this manner:

 SELECT SUBSTRING_INDEX('10.159.101.0-10.159.101.255','-',1)as string1

How can I accomplish the same thing in SQL? I'd like to split the IP range into two strings based on the - delimiter which can vary in position due to the nature of IP addresses.

Upvotes: 1

Views: 7511

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

In SQL Server you would use SUBSTRING and CHARINDEX as demonstrated in Matt's answer, or LEFT and RIGHT like this:

DECLARE @string varchar(50);
SET @string = '10.159.101.0-10.159.101.255';
SELECT LEFT(@string, CHARINDEX('-', @string)-1) As First,
       RIGHT(@string, LEN(@string) - CHARINDEX('-', @string)) As Second

Upvotes: 1

Matt
Matt

Reputation: 15071

Use a combination of SUBSTRING, CHARINDEX & LEN.

SELECT yourfield,
SUBSTRING(yourfield, 1,CHARINDEX('-', yourfield)-1) LEFTSIDE,
SUBSTRING(yourfield, CHARINDEX('-', yourfield)+1, LEN(yourfield)) RIGHTSIDE
FROM yourtable

Output

yourfield                   LEFTSIDE      RIGHTSIDE
10.159.101.0-10.159.101.255 10.159.101.0  10.159.101.255

SQL Fiddle: http://sqlfiddle.com/#!6/cb36f/3/0

Upvotes: 4

Related Questions