Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extract Data using subscript & charindex

Author  Topic 

jtree77
Starting Member

4 Posts

Posted - 2014-04-21 : 14:46:43
I am trying to extract the IP address from a command string, the problem I am running into is there are 3 types of command strings.

SENDAUTO IP192.168.1.32L0O11 Z1 5(E=00:00,F=00:00,G=00:00,H=00:00,I=00:00,J=00:00)

SENDCREATEEXCEPTION -1,IP192.168.1.32,0,11,0,Z1,Free text-label,19,3,19,3,06:00|24:00,I|O,1,288003,1

SENDWEEKTIMES IP192.168.1.32,0,11,Z1,3,100,23:00|24:00|24:00|24:00|24:00|24:00,I|O|O|O|O|O

The IP address length can vary, and the character after the IP is either a L or ,

How can I edit the following function to look for both characters?
SUBSTRING(Command, CHARINDEX('IP', Command), CHARINDEX('L', Command) - (CHARINDEX('IP', Command)))


gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-21 : 14:59:28
You could replace CHARINDEX('L', Command) with something like (untested):


CASE SUBSTRING(COMMAND, 1, 5)
WHEN 'SENDA' then CHARINDEX('L', Command)
ELSE CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command)))
END
Go to Top of Page

jtree77
Starting Member

4 Posts

Posted - 2014-04-22 : 10:36:51
That works for 2 of the examples but fails on

SENDCREATEEXCEPTION -1,IP192.168.1.32,0,11,0,Z1,IQ3-Free text-zonelabel,19,3,19,3,06:00|24:00,I|O,1,288003,1

I think it is due to the comma before the IP address, how do I handle it when there are 2 commas?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-22 : 11:06:23
quote:
Originally posted by jtree77

That works for 2 of the examples but fails on

SENDCREATEEXCEPTION -1,IP192.168.1.32,0,11,0,Z1,IQ3-Free text-zonelabel,19,3,19,3,06:00|24:00,I|O,1,288003,1

I think it is due to the comma before the IP address, how do I handle it when there are 2 commas?



Try this:


select len(Command), SUBSTRING(Command, CHARINDEX('IP', Command),


CASE SUBSTRING(Command, 1, 5)
WHEN 'SENDA' then CHARINDEX('L', Command) - (CHARINDEX('IP', Command))
ELSE CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command)))-1
END

)
Go to Top of Page

jtree77
Starting Member

4 Posts

Posted - 2014-04-22 : 13:51:08
I get an error saying "invalid perimeter passed to left" Here is the current query, am I doing something wrong? It still seems to fail on the command with the , before IP address.


SELECT Command,

SUBSTRING(Command, CHARINDEX('IP', Command) + 2, (CASE SUBSTRING(COMMAND, 1, 5)
WHEN 'SENDA' THEN
CHARINDEX('L', Command)
WHEN 'SENDC' THEN
CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command))) - 1
ELSE
CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command))) END) - CHARINDEX('IP', Command) + 2) AS Expr1
FROM
dbo.Actions

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-22 : 14:40:11
OK -- there are two problems here. The obvious one:

After the case, you are should wrap

- (CHARINDEX('IP', Command) + 2

in parenthesis like this:

 - (CHARINDEX('IP', Command) + 2)


(Basically you're adding two when you should be subtracting it :).

Second, the two substring expression effectively set a new starting point for charindex. That is, charindex is no longer starting from the start of the column. Hence, the computed length is too short. I fixed both problems like this:


SELECT Command,

SUBSTRING(Command, CHARINDEX('IP', Command) + 2,
(CASE SUBSTRING(COMMAND, 1, 5)
WHEN 'SENDA' THEN CHARINDEX('L', Command)
WHEN 'SENDC' THEN
CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command)))- 1 + CHARINDEX('IP', Command)
ELSE
CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command)))- 1 + CHARINDEX('IP', Command)
END) - (CHARINDEX('IP', Command) + 2)) AS Expr1



I have a feeling that this could be simplified a bit. So, here it is reformatted:


SELECT Command,

SUBSTRING(Command, CHARINDEX('IP', Command) + 2,
(CASE SUBSTRING(COMMAND, 1, 5)
WHEN 'SENDA' THEN CHARINDEX('L', Command) - (CHARINDEX('IP', Command) + 2)
WHEN 'SENDC' THEN
CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command)))-3
ELSE
CHARINDEX(',', SUBSTRING(Command, CHARINDEX('IP', Command), LEN(Command)))-3
END)) AS Expr1


Note that the second and third cases are identical. You could remove the second case altogether if you like
Go to Top of Page

jtree77
Starting Member

4 Posts

Posted - 2014-04-22 : 15:05:13
Thank you so much, this works perfectly and I appreciate the explanation, I understand it now.
Go to Top of Page
   

- Advertisement -