| Author |
 Topic  |
|
abk
Starting Member
29 Posts |
 Posted - 03/29/2012 : 06:14:23
|
no. there is no way , we can say which data belongs to which device.
To get Unitno from my string
UnitNo = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 1), ' ', CmdStr)
else dbo.fnParseString(-((num * 11) + 1), ',', CmdStr)
Is this not OK ? Can you please tell me what I need to change or adjust as I am not able to figure out. The delimiter is ' ' between the strings. Is '1' the 1st position. |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/29/2012 : 06:33:58
|
then how can you differentiate the last one from the previous two ?
As you have mention in the beginning of this thread
quote:
Originally posted by abk
We have GPS devices which send data in many different formats . The formats of the packets and the length of the data
packet varies. The delimiter string also is different ( could be , or pipe)
You will need to find a way to identify the various format and apply the corresponding parsing logic to it.
KH
Time is always against us
|
Edited by - khtan on 03/29/2012 06:38:04 |
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/29/2012 : 07:04:08
|
Ok. thanx
Is it possible to just explain the logic of thq query you posted ?
If it is not of use for this purpose, as a newbie to this , I would like to understand the logic.
Thanks
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/29/2012 : 07:29:02
|
which part of the query are you referring to ?
KH
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/29/2012 : 09:00:14
|
For the known 3 different format
Format 1 : 357464031469825,183615.00,1256.3340,0,0
Format 2 : ^|1|1|1259.43332|07735.37427|35202402773555
Format 3 : 'FC03 00140700 00007D04 0002E702 0001624F 00C02A9D 049C5D4C 00000014 00025A39 00000008 000FE348'
Format 1 2 3
Delimiter : , | <space>
No of Field : 5 5 11
I have try to generalize the query. Try this.
select CmdStr,
strfmt,
no_of_field,
no_of_set,
UnitNo = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 1), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 6), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 1), ' ', CmdStr)
end,
lat = case strfmat
when 1 then dbo.fnParseString(-((num * no_of_field) + 2), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 4), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 6), ' ', CmdStr)
end,
lon = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 3), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 5), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 7), ',', CmdStr)
end,
. . .
from (
select CmdStr, strfmt, no_of_field,
no_of_set = case strfmt
when 1 then (len(CmdStr) - len(replace(CmdStr, ',', '')) + 1) / no_of_field
when 2 then (len(CmdStr) - len(replace(CmdStr, '|', ''))) / no_of_field
when 3 then (len(CmdStr) - len(replace(CmdStr, ' ', '')) + 1) / no_of_field
end
from
(
select CmdStr,
strfmt = case when CmdStr like '[0-9]%' then 1
when CmdStr like '^%' then 2
when CmdStr like '[A-Z]%' then 3
end,
no_of_field = case when CmdStr like '[0-9]%' then 5
when CmdStr like '^%' then 5
when CmdStr like '[A-Z]%' then 11
end
from RawDataPackets
) r
) t
inner join numbers n on n.num >= 0
and n.num < no_of_set
The is no way to process these data if you are unable to determine what is the format of the gps data.
What i have done in red is depending on the data string itself. If it is begins with numeric or ^ or alpha. I believe you will have much more different format and it will not be possible to determine the format of the data this way.
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/30/2012 : 00:20:40
|
Many many thanks for helping . I agree that I must find a way to identify the format , otherwise generalizing will be hard .
Thanks again |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/30/2012 : 00:32:14
|
yes. You can't depend on the gps data string to determine the format. It is not reliable. If you have a device ID or device type etc that comes together with the gps data string, then it might be possible to use that to determine the format and how to process it.
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/02/2012 : 01:37:44
|
Is it possible to assign unitno, lat, lan etc to local variables as well as get as a result set?
I tried to assign within select and got the error
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/02/2012 : 02:41:51
|
Nope. you can't do that. You can't mix that.
If you have multiple records in the result, and assigning it to local variable, you will only get one rec assign to the variable
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/02/2012 : 05:56:10
|
| If i want to use every record for further processing, what would be a good way to process this data. I want to use every record of the select (Unitno, lat, lan...etc) and send them as parameters to another stored procedure. Performance is really important as the number of records will be huge. Any ideas? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/02/2012 : 06:14:23
|
Depending what are you doing. It will not be efficient if you send record by record to another stored procedure for processing. Best is let the stored procedure access to the table directly, parse it (UnitNo, Lat, lan etc) and the process it.
Or you have have a stored procedure process the raw data into another table or temp table or table variable and then the process it from there on
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/03/2012 : 03:07:02
|
Thanks. Does fnParseString accept strings which are within the delimiter < > ? Eg:
id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312> |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/03/2012 : 03:15:42
|
No. Try using >< as delimiter
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/03/2012 : 06:22:42
|
>< does not seem to work.
The query and sample string I tried .
Sample String
^id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312><0.000><0.0><22><06><0><0><67><0><0><0.09><0.0><5.73><2.2><0><0.0>@
^ID=UnitNo&POS=<VersionNo><L1><L2><Time><Date><Speed><Heading><NoOfVS> <Ig><o1><i2><i2><i3><a1><v1><v2><v3><v4><v5>
select
CmdStr,
strfmt,
no_of_field,
no_of_set,
UnitNo = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 1), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 19), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 2), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 1), '><', CmdStr)
end,
Pos = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 5), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 1), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 4), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 2), '><', CmdStr)
end,
L1 = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 3), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 9), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 1), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 3), '><', CmdStr)
end,
L2 = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 6), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 2), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 1), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 4), '><', CmdStr)
end,
…
from(
select CmdStr, strfmt, no_of_field,
no_of_set = case strfmt
when 1 then (len(CmdStr) - len(replace(CmdStr, ',', '')) + 1)
when 2 then (len(CmdStr) - len(replace(CmdStr, '|', '')) + 1)
when 3 then (len(CmdStr) - len(replace(CmdStr, ' ', '')) + 1)
when 4 then (len(CmdStr) - len(replace(CmdStr, '><', ' ')) +1 )
end
from
(
select CmdStr,
strfmt= case when CmdStr like '[0-9]%' then 1
when CmdStr like '^%' then 2
when CmdStr like '[A-Z]%' then 3
when CmdStr like '^id' then 4
end,
no_of_field = case when CmdStr like '[0-9]%' then 5
when CmdStr like '^%' then 19
when CmdStr like '[A-Z]%' then 11
when CmdStr like '^id' then 20
end
from RawDataPackets
) r
) t
inner join numbers n on n.num >= 0
and n.num < no_of_set
end
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/03/2012 : 11:25:45
|
yes. fnParseString expects the fnParseString to be single char only.
make the below changes to the fnParseString
@Delimiter VARCHAR(10)
...
IF @Section > 0
SELECT @Text = REVERSE(@Text),
@Delimiter = REVERSE(@Delimiter)
...
WHILE @NextPos > 0 AND ABS(@Section) <> @Found
SELECT @LastPos = @NextPos + LEN(@Delimiter) - 1,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + LEN(@Delimiter)),
@Found = @Found + 1
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/03/2012 : 23:58:33
|
Thanks . Made the changes as per your suggestion. Still no luck. Now using '><' as delimter . Is that right?
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/04/2012 : 00:17:55
|
yes. Use '><' as delimiter. But it is not as simple as that.
quote:
Sample String
^id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312><0.000><0.0><22><06><0><0><67><0><0><0.09><0.0><5.73><2.2><0><0.0>@
in your sample string, you have some data infront "id=????&POS=???" does not looks like the value that you want. My guess is the UnitNo, Lat, Lon value should be those in <> right ? Then you will need to do further processing on that string and only pass in that section of string in red into the fnParseString
Also you missed out a '%' here
select CmdStr,
strfmt= case when CmdStr like '[0-9]%' then 1
when CmdStr like '^%' then 2
when CmdStr like '[A-Z]%' then 3
when CmdStr like '^id%' then 4
end,
KH
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/04/2012 : 00:31:26
|
also, "^id%" need to come before "^%" or it will never hit that condition
select CmdStr,
strfmt= case when CmdStr like '[0-9]%' then 1
when CmdStr like '^id%' then 4
when CmdStr like '^%' then 2
when CmdStr like '[A-Z]%' then 3
end,
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/04/2012 : 02:07:02
|
I need the Id field for UnitNo . So I changed the When 4 lines ( in red) . I tested it outside and it did work. But I get compilation error when I used it with the 'when'.
Works fine as queries
=========================================================
(SELECT CHARINDEX('=','^id=PTTRK30ES861001000245502&POS=')
select PATINDEX('%&POS=%','^id=PTTRK30ES861001000245502&POS=')
Select SUBSTRING('^id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312><0.000><0.0><22><06><0><0><67><0><0><0.09><0.0><5.73><2.2><0><0.0>@',CHARINDEX('<','^id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312><0.000><0.0><22><06><0><0><67><0><0><0.09><0.0><5.73><2.2><0><0.0>@'),LEN('^id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312><0.000><0.0><22><06><0><0><67><0><0><0.09><0.0><5.73><2.2><0><0.0>@'))
Select SUBSTRING('^id=PTTRK30ES861001000245502&POS=',(CHARINDEX('=','^id=PTTRK30ES861001000245502&POS=')+1),(PATINDEX('%&POS=%','^id=PTTRK30ES861001000245502&POS=')-5))
)
In SP, i get error :
Msg 156, Level 15, State 1, Procedure ParseRawData, Line 156
Incorrect syntax near the keyword 'end'.
Msg 156, Level 15, State 1, Procedure ParseRawData, Line 268
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Procedure ParseRawData, Line 296
Incorrect syntax near 't'.
=============================================================
select
CmdStr,
strfmt,
no_of_field,
no_of_set,
UnitNo = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 1), ',', CmdStr)when 2 then dbo.fnParseString(-((num * no_of_field) + 19), '|',CmdStr)when 3 then dbo.fnParseString(-((num * no_of_field) + 1), ' ', CmdStr)when 4 then (SUBSTRING(Cmdstr,(CHARINDEX('=',CmdStr)+1),(PATINDEX('%&POS=%',Cmdstr)-5))
end,
lat = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 2), ',', CmdStr)when 2 then dbo.fnParseString(-((num * no_of_field) + 3), '|',CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 6), ' ', CmdStr)when 4 then dbo.fnParseString(-((num * no_of_field) + 1), '><',select SUBSTRING(Cmdstr,CHARINDEX('<',CmdStr),LEN(CmdStr))
end |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/04/2012 : 02:21:29
|
remove the "SELECT" keyword from the lat section for strfmt = 4
KH
Time is always against us
|
|
|
|
Topic |
|
|
|
stored procedure to parse bulk strings in varied f
