| Author |
 Topic  |
|
abk
Starting Member
29 Posts |
 Posted - 03/28/2012 : 02:57:34
|
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)
Examples :
357464031469825,183615.00,1256.3340,0,0
^|1|1|1259.43332|07735.37427|35202402773555
These strings are stored in a db table( rawtable) with the timestamp and the table can have 15-20 million records.
The strings must be parsed and packed,but the way in which they appear in the raw data packet differs . So,
we need to parse depending on format of packet.
Raw table can contains about 15-20 million records . Keeping performance in mind, how can I parse these strings depending on varied format in a sql server stored procedure
Eg: I need to get an output like
For this string 357464031469825,183615.00,1256.3340,0,0
output must be
@UnitNo = 357464031469825,
@Lat = 183615.00
@Lon = 1256.3340,
@IO1 = 0
@IO2 = 0;
For this string ^|1|1|1259.43332|07735.37427|35202402773555
output must be
@UnitNo=35202402773555
@lat=1259.43332
@lon=07735.37427
@IO1=1
@IO2=1
As you can see the position in which they appear in the two strings are different. How can I parse it and populate
the variables correctly using sql server store procedure. Since the raw data table has many records , I need a
procedure which is fast. Many thanks in advance
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 03:08:51
|
use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
select UnitNo = case when gps_data like '^%'
then dbo.fnParseString( 1, '|', gps_data)
else dbo.fnParseString(-1, ',', gps_data)
end,
lat = case when gps_data like '^%'
then dbo.fnParseString(-4, '|', gps_data)
else dbo.fnParseString(-2, ',', gps_data)
end,
. . .
KH
Time is always against us
|
 |
|
|
abk
Starting Member
29 Posts |
Posted - 03/28/2012 : 03:23:24
|
fnParseString returned error
Msg 8116, Level 16, State 1, Procedure fnParseString, Line 16
Argument data type text is invalid for argument 1 of reverse function. |
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/28/2012 : 03:28:00
|
| Also,depending upon the device the position of unitno in the string can vary. We have about 50 devices right now( could increase) and also the number of substrings can be upto 42( unitno, lat,lan is just an example) . So I will need a general procedure which will be able to parse based on a format. I hope I am able to clearly state what I am looking for. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 03:28:08
|
change to varchar(max) if you are using SQL 2005/2008
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/28/2012 : 04:59:01
|
Thanks . My worry is the no of devices is more and also the delimiter separated substrings will be 42. So is it possible to map position to substring for a particular device. ANy more ideas will really help
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 05:07:29
|
42 what is 42 ?
if you have a device type, then you can based on the device type to decide what decoding method to use
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/28/2012 : 05:24:10
|
Example :
String A can be
^0609|8|2829.49673|07704.82322|13.627|130.59|140312075012|2|0|A|1|0|0|0|3.015600|12.230000|2748.26|76373.00|352024027728165|1212|......
So the number of substrings in string A can be upto 42.
And there are 50 devices ( so 50 formats where position of the substrings will differ )
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 05:37:39
|
^0609|8|2829.49673|07704.82322|13.627|130.59|140312075012|2|0|A|1|0|0|0|3.015600|12.230000|2748.26|76373.00|352024027728165|1212|......
it will be always 5 value per device ? Can you help to identify which value are belong to a set ?
KH
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 05:56:23
|
select gps_data,
no_of_sets = case when gps_data like '^%'
then (len(gps_data) - len(replace(gps_data, '|', ''))) / 5
else (len(gps_data) - len(replace(gps_data, ',', '')) + 1) / 5
end,
UnitNo = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 6), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 1), ',', gps_data)
end,
lat = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 4), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 2), ',', gps_data)
end,
lon = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 5), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 3), ',', gps_data)
end,
IO1 = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 3), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 4), ',', gps_data)
end,
IO2 = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 2), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 5), ',', gps_data)
end
from yourtable t
inner join numbers n on n.num >= 0
and n.num < case when gps_data like '^%'
then (len(gps_data) - len(replace(gps_data, '|', ''))) / 5
else (len(gps_data) - len(replace(gps_data, ',', '')) + 1) / 5
end
numbers is a number table contain value 0, 1, 2, 3 etc
create table numbers (num int)
declare @num int
select @num = 0
while @num < 10000
begin
insert into numbers (num) select @num
select @num = @num + 1
end
KH
Time is always against us
|
Edited by - khtan on 03/28/2012 05:57:05 |
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/28/2012 : 06:06:56
|
Sorry for any confusion .
I will explain my requirement again.
I have a rawdatatable which has abt 20 million records
The table has one varchar column
sample record is
^0609|11|2835.11552|07720.85664|6.6891|50.012|210312054133|2|3|A|1|0|0|0|0.632900|12.788500|67825.45|66883.00|352024027735038|1213.1|455|2323|8987|343|5454|0|0|0|0|0|0|1|11|11|1|33|0|0|0|0|0
Here we want to parse this record and assign values eg:
@unitid=210312054133 (position 7)
@cmdid=0609
@io1=11
@lat=1212
... and so on ( all the 42 substrings of this must be assigned)
This is for 1 device.
I can have another device which will send rawdata where each record will have 42 substrings as part of the long data packet/ But the position will be different.
Eg:
String B from another device
357464031469825,1212,GPRMC,183615.00,A,1256.3340,N,08011.1400,E,0.0,0.0,190312,,,A*51,0,5014,0,0,0,0,0,0,0,0,0,0,0,12,1212,0,0,0,0,0,0,0,0,0,1,1212
Note that the delimter is , in this case and
@unitid=357464031469825 ( position 1)
@cmdid=1212 ( psoition 2)
So I will need all 42 substrings of every data packet packed respectively into same variables like (unitid,cmdid,io1,io2....... etc) , only the position they appear is different
There can be atleast 50 format ( position diff) for now.
I want a general procedure which will handle these formats and also be fast as the no of records in the table is large.
Hope I am clear now.
Thanks in advance
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 06:12:54
|
it will be easier if you can accept the result in rows rather than column as what i have shown in my last post. Just use the same technique and adjust the position accordingly
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/28/2012 : 06:19:54
|
It will be 42 values per device . 42 values in different positions dependent on device
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/28/2012 : 07:50:06
|
quote:
Originally posted by abk
It will be 42 values per device . 42 values in different positions dependent on device
see the query Posted - 03/28/2012 : 05:56:23
it should be able to do the job
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/29/2012 : 05:01:35
|
Many thanks for the query. I need help in understanding this query .
I have a data packet string in this format for a new device in hex format.( delimter is space)
They are in hex and I need to convert to decimal
FC03 00140700 00007D04 0002E702 0001624F 00C02A9D 049C5D4C 00000014 00025A39 00000008 000FE348
<UnitNo> -> FC03(64515)
<PACKET_HEADER> ->00140700
<PACKET_NUMBER> ->00007D04 (32004)
<DATE> -> 0002E702(190210)
<TIME> -> 0001624F(90703)
<lat> -> 00C02A9D(12593821)
<lan> -> 049C5D4C(77356364)
<speed> ->00000014(20)
<pulseodometer> -> 00025A39 (
<RESERVED> -> 00000008
<IO1> -> 000FE348
How should i modify the query? I have 11 fields . |
Edited by - abk on 03/29/2012 05:05:45 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
|
|
abk
Starting Member
29 Posts |
Posted - 03/29/2012 : 05:21:12
|
Thanks
select gps_data,
no_of_sets = case when gps_data like '^%'
then (len(gps_data) - len(replace(gps_data, '|', ''))) / 5
else (len(gps_data) - len(replace(gps_data, ',', '')) + 1) / 5
end,
UnitNo = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 6), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 1), ',', gps_data)
end,
lat = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 4), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 2), ',', gps_data)
end,
lon = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 5), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 3), ',', gps_data)
end,
IO1 = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 3), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 4), ',', gps_data)
end,
IO2 = case when gps_data like '^%'
then dbo.fnParseString(-((num * 5) + 2), '|', gps_data)
else dbo.fnParseString(-((num * 5) + 5), ',', gps_data)
In the above query is '5' the maximum no of substrings and are other numbers the position of the substring ?
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/29/2012 : 05:32:25
|
If cmdstr = 'FC03 00140700 00007D04 0002E702 0001624F 00C02A9D 049C5D4C 00000014 00025A39 00000008 000FE348'
select CmdStr,
no_of_sets = case when CmdStr like '^%'
then (len(CmdStr) - len(replace(CmdStr, '', ''))) / 11
//else (len(CmdStr) - len(replace(CmdStr, ',', '')) + 1) / 11
end,
UnitNo = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 1), '', CmdStr)
//else dbo.fnParseString(-((num * 11) + 1), ',', CmdStr)
end,
packet_header= case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 2), '', CmdStr)
// else dbo.fnParseString(-((num * 11) + 2), ',', CmdStr)
end,
packet_number = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 3), ' ', CmdStr)
// else dbo.fnParseString(-((num * 11) + 3), ',', CmdStr)
end,
datef = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 4), ' ', CmdStr)
// else dbo.fnParseString(-((num * 11) + 4), ',', CmdStr)
end,
timef = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 5), ' ', CmdStr)
// else dbo.fnParseString(-((num * 11) + 5), ',', CmdStr)
end
lat= case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 6), ' ', CmdStr)
// else dbo.fnParseString(-((num * 11) + 6), ',', CmdStr)
end
from RawDataPackets
inner join numbers n on n.num >= 0
and n.num < case when CmdStr like '^%'
then (len(CmdStr) - len(replace(CmdStr, '', ''))) / 11
else (len(CmdStr) - len(replace(CmdStr, ',', '')) + 1) / 11
end
end
==============================================================================
Is the above query correct to get
unitno packet_header packet_number datef timef lat
FC03 00140700 00007D04 0002E702 0001624F 00C02A9D |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/29/2012 : 05:33:37
|
you need to change this accordingly
the earlier is only 5 value per sets, this new one is 11 fields.
no_of_sets = case when gps_data like '^%'
then (len(gps_data) - len(replace(gps_data, '|', ''))) / 5
else (len(gps_data) - len(replace(gps_data, ',', '')) + 1) / 5
end,
if you have a Device Type or something to identify various format, use it to.
then (len(gps_data) - len(replace(gps_data, '|', ''))) / case when device = 'device-1' then 5
when device = 'device-2' then 5
when device = 'device-3' then 11
end
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 03/29/2012 : 05:52:07
|
I did change to 11 , but I am not able to get the result. Dont know if I am missing something. I am just trying it on one string only
FC03 00140700 00007D04 0002E702 0001624F 00C02A9D 049C5D4C 00000014 00025A39 00000008 000FE348
=========================================================
The query used
select CmdStr,
no_of_sets = case when CmdStr like '^%'
then (len(CmdStr) - len(replace(CmdStr, '', ''))) / 11
else (len(CmdStr) - len(replace(CmdStr, ',', '')) + 1) / 11
end,
UnitNo = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 1), '', CmdStr)
else dbo.fnParseString(-((num * 11) + 1), ',', CmdStr)
end,
packet_header= case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 2), '', CmdStr)
else dbo.fnParseString(-((num * 11) + 2), ',', CmdStr)
end,
packet_number = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 3), '', CmdStr)
else dbo.fnParseString(-((num * 11) + 3), ',', CmdStr)
end,
datef = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 4), '', CmdStr)
else dbo.fnParseString(-((num * 11) + 4), ',', CmdStr)
end,
timef = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 5), '', CmdStr)
else dbo.fnParseString(-((num * 11) + 5), ',', CmdStr)
end
from RawDataPackets
inner join numbers n on n.num >= 0
and n.num < case when CmdStr like '^%'
then (len(CmdStr) - len(replace(CmdStr, '', ''))) / 11
else (len(CmdStr) - len(replace(CmdStr, ',', '')) + 1) / 11
end
end
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/29/2012 : 06:01:38
|
that 11 fields is only part of the changes.... you still need to change the rest correspondingly.
do you have a device type in your table that can identity which gps data is from which device type ?
KH
Time is always against us
|
|
|
|
Topic |
|
|
|
stored procedure to parse bulk strings in varied f
