| Author |
 Topic  |
|
abk
Starting Member
29 Posts |
 Posted - 04/04/2012 : 02:50:55
|
Thanks . Removed select. Now query is as follows. But still gives
error
============================================================
Msg 156, Level 15, State 1, Procedure ParseRawData, Line 154
Incorrect syntax near the keyword 'end'.
Msg 102, Level 15, State 1, Procedure ParseRawData, Line 192
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), '><', (SUBSTRING(Cmdstr,CHARINDE('<',CmdStr),LEN(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 4 then (len(CmdStr) - len(replace(CmdStr, '><', ' ')) +1 ) / no_of_field
when 2 then (len(CmdStr) - len(replace(CmdStr, '|', '')) + 1) / 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 '^id%' then 4
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 '^id%' then 20
when CmdStr like '^%' then 19
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
end |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/04/2012 : 03:06:23
|
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), '><', (SUBSTRING(Cmdstr,CHARINDEX('<',CmdStr),LEN(CmdStr))))
end
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/04/2012 : 04:34:01
|
I had the X for CHARINDEX in my SP, but dont know why it was missing when I posted it. But one bracket was missing and that worked . Thanks so much once again.
I had a thought about parsing the different strings:
To have a table which will hold format strings
Have formats defined for Strings ( as shown below)
For String1
^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>@
Format1
^ID=unitid&POS=<VersionNo><Lat><Lon><Time><Date><Speed> .... etc
Can we match a string with its format and dump the values into table ( column names in format string correspond to column names in the table)
I am not sure if this is a workable idea and how to even do it. Please let me know .
Thanks a lot |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/04/2012 : 05:00:15
|
It will not be easy to that. You will need to parse the format definition to determine where is the tag value (Lat, Lon etc) and then use this information to parse the actual cmdstr.
You can however setup one
- StrFormat int
- Delimiter varchar(10)
- Version int -- element no of the version tag
- Lat int -- element no of the latitute tag
- ....
than your query can be generalize. So for every different format you need to setup an entry in this table.
And probably after this, your problem is to identify the format from the cmdstr, or unless you have another format that the current query is not catered for.
KH
Time is always against us
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/04/2012 : 05:46:25
|
Thanks again . I will work on the idea you suggested. Many thanks
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/05/2012 : 01:46:00
|
I have a doubt about no_of_set fie;ld. For S1 , and S2
no_of_set = 1 , so I get 1 row of data for each. But for S3, I get no_of_fields = no_of_sets ( 19 rows of data, first row has the values and 2-19 have null values)
Am I doing something wrong in the query.
I have three records ( strings)
S1
^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>@
S2
^0609|1|2834.56150|07710.32664|17.170|330.60|210312054103|2|3|A|1|0|0|0|0.856200|0.000000|1770.54|393.00|352024027743149|!
S3
FC03 00140700 00007D04 0002E702 0001624F 00C02A9D 049C5D4C 00000014 00025A39 00000008 000FE348
select
CmdStr,
strfmt,
no_of_field,
no_of_set,
UnitNo = case strfmt
when 1 then dbo.fnParseStringSingleChar(-((num * no_of_field) + 1), ',', CmdStr)
when 2 then dbo.fnParseStringSingleChar(-((num * no_of_field) + 19), '|', CmdStr)
when 3 then dbo.ConvertFromBase(dbo.fnParseStringSingleChar(-((num * no_of_field) + 1), ' ', CmdStr), 16)
when 4 then (SUBSTRING(Cmdstr,(CHARINDEX('=',CmdStr)+1),(PATINDEX('%&POS=%',Cmdstr)-5)))
end,
lat = case strfmt
when 1 then dbo.fnParseStringSingleChar(-((num * no_of_field) + 2), ',', CmdStr)
when 2 then dbo.fnParseStringSingleChar(-((num * no_of_field) + 3), '|', CmdStr)
when 3 then dbo.ConvertFromBase(dbo.fnParseStringSingleChar(-((num * no_of_field) + 6), ' ', CmdStr), 16)
end,
lon = case strfmt
when 1 then dbo.fnParseStringSingleChar(-((num * no_of_field) + 3), ',', CmdStr)
when 2 then dbo.fnParseStringSingleChar(-((num * no_of_field) + 4), '|', CmdStr)
when 3 then dbo.ConvertFromBase(dbo.fnParseStringSingleChar(-((num * no_of_field) + 7), ' ', CmdStr), 16)
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, '|', '')) )
when 3 then (len(CmdStr) - len(replace(CmdStr, ' ', '')) + 1) / no_of_field
when 4 then (len((SUBSTRING(Cmdstr,(CHARINDEX('=',CmdStr)+1),(PATINDEX('%&POS=%',Cmdstr)-5)))) - len(replace((SUBSTRING(Cmdstr,(CHARINDEX('=',CmdStr)+1),(PATINDEX('%&POS=%',Cmdstr)-5))), '><', '')) +1)
end
from
(
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,
no_of_field = case when CmdStr like '[0-9]%' then 5
when CmdStr like '^id%' then 20
when CmdStr like '^%' then 19
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
|
|
|
|
abk
Starting Member
29 Posts |
Posted - 04/05/2012 : 03:14:18
|
As per your suggested I created the following tables and populated them:
Keeping in mind the 3 string formats, I created some fields in the packetformat table and rawdatabacket table will hold the exact strings.
Format 1
^0609|1|2834.56150|07710.32664|17.170|330.60|210312054103|2|3|A|1|0|0|0|0.856200|0.000000|1770.54|393.00|352024027743149|!
Format 2 :
FC03 00140700 00007D04 0002E702 0001624F 00C02A9D 049C5D4C 00000014 00025A39 00000008 000FE348
Format 3 :
^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>@
create table packetfmt (
StrFormat int,
Delimiter varchar(10)null,
[UnitNo] int,
[Lat] int,
[Lon] int,
[TrackDate] int,
[Speed] int,
[Direction] int,
)
insert into packetfmt values( 1,'|',19,3,4,7,5,6)
insert into packetfmt values(2,' ',1,4,5,6,7,8)
insert into packetfmt values (3,'><',1,3,4,5,6,7)
The Format 3 string has ^id=PTTRK30ES861001000245502&POS= ( id field is unitno field ) Can this be incorporated also in the generalized query?
How can I write a generalized query which will use this table packetfmt positions ? thanks |
|
|
|
Topic |
|
|
|
stored procedure to parse bulk strings in varied f
