Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Other Development Tools
 stored procedure to parse bulk strings in varied f

Author  Topic 

abk
Starting Member

29 Posts
Posted - 2012-03-28 : 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)

17689 Posts
Posted - 2012-03-28 : 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
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-28 : 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.
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-28 : 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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-28 : 03:28:08
change to varchar(max) if you are using SQL 2005/2008

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-28 : 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
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-28 : 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
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-28 : 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 )
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-28 : 05:37:39
[code]
^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|......
[/code]
it will be always 5 value per device ? Can you help to identify which value are belong to a set ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-28 : 05:56:23
[code]
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
[/code]

numbers is a number table contain value 0, 1, 2, 3 etc
[code]
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
[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-28 : 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
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-28 : 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
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-28 : 06:19:54
It will be 42 values per device . 42 values in different positions dependent on device
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-28 : 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
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-29 : 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 .
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-29 : 05:12:25
first, you use the same function fnParseString() to parse the string, pass in ' ' as delimiter. Then you need to convert the hex string to decimal.

Here are a couple of them from google result
http://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/
http://beyondrelational.com/modules/2/blogs/70/posts/10888/converting-hexadcimal-to-decimal.aspx

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-29 : 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 ?
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-29 : 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
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-29 : 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
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

abk
Starting Member

29 Posts
Posted - 2012-03-29 : 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
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-29 : 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
[spoiler]Time is always against us[/spoiler]
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site