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
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

abk
Starting Member

29 Posts

Posted - 03/28/2012 :  02:57:34  Show Profile  Reply with Quote
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
17650 Posts

Posted - 03/28/2012 :  03:08:51  Show Profile  Reply with Quote
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

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/28/2012 :  03:23:24  Show Profile  Reply with Quote

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 - 03/28/2012 :  03:28:00  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 03/28/2012 :  03:28:08  Show Profile  Reply with Quote
change to varchar(max) if you are using SQL 2005/2008


KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/28/2012 :  04:59:01  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 03/28/2012 :  05:07:29  Show Profile  Reply with Quote
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

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/28/2012 :  05:24:10  Show Profile  Reply with Quote

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)

Singapore
17650 Posts

Posted - 03/28/2012 :  05:37:39  Show Profile  Reply with Quote

^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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 03/28/2012 :  05:56:23  Show Profile  Reply with Quote

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

abk
Starting Member

29 Posts

Posted - 03/28/2012 :  06:06:56  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 03/28/2012 :  06:12:54  Show Profile  Reply with Quote
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

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/28/2012 :  06:19:54  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 03/28/2012 :  07:50:06  Show Profile  Reply with Quote
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

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/29/2012 :  05:01:35  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 03/29/2012 :  05:12:25  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 03/29/2012 05:13:33
Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/29/2012 :  05:21:12  Show Profile  Reply with Quote
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 - 03/29/2012 :  05:32:25  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 03/29/2012 :  05:33:37  Show Profile  Reply with Quote
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

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/29/2012 :  05:52:07  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 03/29/2012 :  06:01:38  Show Profile  Reply with Quote
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

Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next 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.66 seconds. Powered By: Snitz Forums 2000