SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 stored procedure to parse bulk strings in varied f
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

abk
Starting Member

29 Posts

Posted - 04/04/2012 :  02:50:55  Show Profile  Reply with Quote

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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 04/04/2012 :  03:06:23  Show Profile  Reply with Quote

	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

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/04/2012 :  04:34:01  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 04/04/2012 :  05:00:15  Show Profile  Reply with Quote
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

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/04/2012 :  05:46:25  Show Profile  Reply with Quote
Thanks again . I will work on the idea you suggested. Many thanks

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/05/2012 :  01:46:00  Show Profile  Reply with Quote

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







Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/05/2012 :  03:14:18  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000