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

abk
Starting Member

29 Posts

Posted - 03/29/2012 :  06:14:23  Show Profile  Reply with Quote
no. there is no way , we can say which data belongs to which device.
To get Unitno from my string
UnitNo = case when CmdStr like '^%'
then dbo.fnParseString(-((num * 11) + 1), ' ', CmdStr)
else dbo.fnParseString(-((num * 11) + 1), ',', CmdStr)

Is this not OK ? Can you please tell me what I need to change or adjust as I am not able to figure out. The delimiter is ' ' between the strings. Is '1' the 1st position.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 03/29/2012 :  06:33:58  Show Profile  Reply with Quote
then how can you differentiate the last one from the previous two ?

As you have mention in the beginning of this thread
quote:
Originally posted by abk

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)



You will need to find a way to identify the various format and apply the corresponding parsing logic to it.


KH
Time is always against us


Edited by - khtan on 03/29/2012 06:38:04
Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/29/2012 :  07:04:08  Show Profile  Reply with Quote
Ok. thanx

Is it possible to just explain the logic of thq query you posted ?
If it is not of use for this purpose, as a newbie to this , I would like to understand the logic.

Thanks

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 03/29/2012 :  07:29:02  Show Profile  Reply with Quote
which part of the query are you referring to ?


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 03/29/2012 :  09:00:14  Show Profile  Reply with Quote
For the known 3 different format

Format 1 : 357464031469825,183615.00,1256.3340,0,0
Format 2 : ^|1|1|1259.43332|07735.37427|35202402773555 
Format 3 : 'FC03 00140700 00007D04 0002E702 0001624F 00C02A9D 049C5D4C 00000014 00025A39 00000008 000FE348'


Format        1     2     3
Delimiter   : ,     |     <space>
No of Field : 5     5     11


I have try to generalize the query. Try this.

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) + 6), '|', CmdStr)
				when 3 then dbo.fnParseString(-((num * no_of_field) + 1), ' ', CmdStr)
                	     	end,
	lat		= case strfmat
				when 1 then dbo.fnParseString(-((num * no_of_field) + 2), ',', CmdStr)
				when 2 then dbo.fnParseString(-((num * no_of_field) + 4), '|', CmdStr)
				when 3 then dbo.fnParseString(-((num * no_of_field) + 6), ' ', CmdStr)
                     		end,
	lon		= case 	strfmt
				when 1 then dbo.fnParseString(-((num * no_of_field) + 3), ',', CmdStr)
				when 2 then dbo.fnParseString(-((num * no_of_field) + 5), '|', CmdStr)
				when 3 then dbo.fnParseString(-((num * no_of_field) + 7), ',', 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 2 then (len(CmdStr) - len(replace(CmdStr, '|', ''))) 	/ 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 '^%' 		then 2
							when CmdStr like '[A-Z]%' 	then 3
							end,
				no_of_field 	= case 	when CmdStr like '[0-9]%' 	then 5
							when CmdStr like '^%' 		then 5
							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


The is no way to process these data if you are unable to determine what is the format of the gps data.

What i have done in red is depending on the data string itself. If it is begins with numeric or ^ or alpha. I believe you will have much more different format and it will not be possible to determine the format of the data this way.


KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 03/30/2012 :  00:20:40  Show Profile  Reply with Quote
Many many thanks for helping . I agree that I must find a way to identify the format , otherwise generalizing will be hard .

Thanks again
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 03/30/2012 :  00:32:14  Show Profile  Reply with Quote
yes. You can't depend on the gps data string to determine the format. It is not reliable. If you have a device ID or device type etc that comes together with the gps data string, then it might be possible to use that to determine the format and how to process it.


KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/02/2012 :  01:37:44  Show Profile  Reply with Quote
Is it possible to assign unitno, lat, lan etc to local variables as well as get as a result set?

I tried to assign within select and got the error


A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 04/02/2012 :  02:41:51  Show Profile  Reply with Quote
Nope. you can't do that. You can't mix that.

If you have multiple records in the result, and assigning it to local variable, you will only get one rec assign to the variable


KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/02/2012 :  05:56:10  Show Profile  Reply with Quote
If i want to use every record for further processing, what would be a good way to process this data. I want to use every record of the select (Unitno, lat, lan...etc) and send them as parameters to another stored procedure. Performance is really important as the number of records will be huge. Any ideas?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 04/02/2012 :  06:14:23  Show Profile  Reply with Quote
Depending what are you doing. It will not be efficient if you send record by record to another stored procedure for processing. Best is let the stored procedure access to the table directly, parse it (UnitNo, Lat, lan etc) and the process it.

Or you have have a stored procedure process the raw data into another table or temp table or table variable and then the process it from there on


KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/03/2012 :  03:07:02  Show Profile  Reply with Quote
Thanks. Does fnParseString accept strings which are within the delimiter < > ? Eg:

id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312>
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 04/03/2012 :  03:15:42  Show Profile  Reply with Quote
No. Try using >< as delimiter


KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/03/2012 :  06:22:42  Show Profile  Reply with Quote
>< does not seem to work.

The query and sample string I tried .

Sample String
^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>@

^ID=UnitNo&POS=<VersionNo><L1><L2><Time><Date><Speed><Heading><NoOfVS> <Ig><o1><i2><i2><i3><a1><v1><v2><v3><v4><v5>


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) + 2), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 1), '><', CmdStr)
end,

Pos = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 5), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 1), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 4), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 2), '><', CmdStr)
end,

L1 = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 3), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 9), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 1), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 3), '><', CmdStr)
end,



L2 = case strfmt
when 1 then dbo.fnParseString(-((num * no_of_field) + 6), ',', CmdStr)
when 2 then dbo.fnParseString(-((num * no_of_field) + 2), '|', CmdStr)
when 3 then dbo.fnParseString(-((num * no_of_field) + 1), ' ', CmdStr)
when 4 then dbo.fnParseString(-((num * no_of_field) + 4), '><', CmdStr)
end,


from(
select CmdStr, strfmt, no_of_field,
no_of_set = case strfmt
when 1 then (len(CmdStr) - len(replace(CmdStr, ',', '')) + 1)
when 2 then (len(CmdStr) - len(replace(CmdStr, '|', '')) + 1)
when 3 then (len(CmdStr) - len(replace(CmdStr, ' ', '')) + 1)
when 4 then (len(CmdStr) - len(replace(CmdStr, '><', ' ')) +1 )
end
from
(
select CmdStr,
strfmt= case when CmdStr like '[0-9]%' then 1
when CmdStr like '^%' then 2
when CmdStr like '[A-Z]%' then 3
when CmdStr like '^id' then 4
end,
no_of_field = case when CmdStr like '[0-9]%' then 5
when CmdStr like '^%' then 19
when CmdStr like '[A-Z]%' then 11
when CmdStr like '^id' then 20
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
17440 Posts

Posted - 04/03/2012 :  11:25:45  Show Profile  Reply with Quote
yes. fnParseString expects the fnParseString to be single char only.

make the below changes to the fnParseString

	@Delimiter 	VARCHAR(10)
...
	IF @Section > 0
		SELECT	@Text = REVERSE(@Text),
			@Delimiter = REVERSE(@Delimiter)
...
	WHILE 	@NextPos > 0 AND ABS(@Section) <> @Found
		SELECT	@LastPos = @NextPos + LEN(@Delimiter) - 1,
			@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + LEN(@Delimiter)),
			@Found = @Found + 1




KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

Posted - 04/03/2012 :  23:58:33  Show Profile  Reply with Quote
Thanks . Made the changes as per your suggestion. Still no luck. Now using '><' as delimter . Is that right?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 04/04/2012 :  00:17:55  Show Profile  Reply with Quote
yes. Use '><' as delimiter. But it is not as simple as that.
quote:

Sample String
^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>@

in your sample string, you have some data infront "id=????&POS=???" does not looks like the value that you want. My guess is the UnitNo, Lat, Lon value should be those in <> right ? Then you will need to do further processing on that string and only pass in that section of string in red into the fnParseString

Also you missed out a '%' here

select CmdStr,
       strfmt= case when CmdStr like '[0-9]%' then 1
                    when CmdStr like '^%' then 2
                    when CmdStr like '[A-Z]%' then 3
                    when CmdStr like '^id%' then 4
                    end,



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 04/04/2012 :  00:31:26  Show Profile  Reply with Quote
also, "^id%" need to come before "^%" or it will never hit that condition


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,



KH
Time is always against us

Go to Top of Page

abk
Starting Member

29 Posts

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

I need the Id field for UnitNo . So I changed the When 4 lines ( in red) . I tested it outside and it did work. But I get compilation error when I used it with the 'when'.


Works fine as queries
=========================================================
(SELECT CHARINDEX('=','^id=PTTRK30ES861001000245502&POS=')

select PATINDEX('%&POS=%','^id=PTTRK30ES861001000245502&POS=')




Select SUBSTRING('^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>@',CHARINDEX('<','^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>@'),LEN('^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>@'))


Select SUBSTRING('^id=PTTRK30ES861001000245502&POS=',(CHARINDEX('=','^id=PTTRK30ES861001000245502&POS=')+1),(PATINDEX('%&POS=%','^id=PTTRK30ES861001000245502&POS=')-5))

)

In SP, i get error :
Msg 156, Level 15, State 1, Procedure ParseRawData, Line 156
Incorrect syntax near the keyword 'end'.
Msg 156, Level 15, State 1, Procedure ParseRawData, Line 268
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Procedure ParseRawData, Line 296
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), '><',select SUBSTRING(Cmdstr,CHARINDEX('<',CmdStr),LEN(CmdStr))
end
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 04/04/2012 :  02:21:29  Show Profile  Reply with Quote
remove the "SELECT" keyword from the lat section for strfmt = 4


KH
Time is always against us

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