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,1SENDWEEKTIMES 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|OThe 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 |
|
|
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,1I think it is due to the comma before the IP address, how do I handle it when there are 2 commas? |
|
|
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,1I 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)))-1END) |
|
|
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 Expr1FROM dbo.Actions |
|
|
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 |
|
|
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. |
|
|
|
|
|