April 24, 2008 at 6:46 am
The query I am passing the two letter strings like "UV+ 131.0000+ 0.0000" work fine when it goes to 3 and up like "TLOV+2048.0000+ 0.0000" it starts messing up the right and middle strings and showing the + sign delimiter.
Please Help !!!
select *,left(raw_payload,charindex('+',raw_payload,1)-1),
substring(left(raw_payload, 1-len(left(raw_payload,charindex('+',raw_payload,1)+1)),
len(raw_payload)),charindex('+',raw_payload,1)* charindex('+',raw_payload,1)-1 ),substring(right(raw_payload,charindex('+',raw_payload,1)*3),charindex('+',right(raw_payload,charindex('+',raw_payload,1)*3),1)+1,len(raw_payload))
from raw_data.dbo
Fieldname: raw_payload
"S9000+"
"UV+ 131.0000+ 0.0000"
"UV+ 132.0000+ 0.0000"
"UV+ 140.0000+ 0.0000"
"UV+ 141.0000+ 0.0000"
"UV+ 142.0000+ 0.0000"
"UVN+ 524.0000+ 0.0000"
"UVN+ 525.0000+ 0.0000"
"UVN+ 527.0000+ 0.0000"
"UVN+ 528.0000+ 0.0000
"TLOV+2044.0000+ 0.0000"
"TLOV+2045.0000+ 0.0000"
"TLOV+2046.0000+ 0.0000"
"TLOV+2047.0000+ 0.0000"
"TLOV+2048.0000+ 0.0000"
"TLOV+2049.0000+ 0.0000"
"OTLWV+2201.0000+ 0.0000"
"OTLWV+2202.0000+ 0.0000"
"OTD/ROV+2401.0000+ 0.0000"
"OTD/ROV+2402.0000+ 0.0000"
"OTD/ROV+2403.0000+ 0.0000"
"OTD/ROV+2404.0000+ 0.0000"
""OTD/ROV+2432.0000+ 0.0000"
"OTD/ROV+2436.0000+ 0.0000"
"OTD/ROV+2443.0000+ 0.0000"
"OTD/ROV+2444.0000+ 0.0000"
"OTD/ROV+2445.0000+ 0.0000"
"OTD/ROV+2446.0000+ 0.0000"
"G59WO+5324.0000+ 36.6190"
"TLMLV+5601.0000+ 0.0000"
"TLMLV+5602.0000+ 20.0000"
"TLMLV+5603.0000+ 0.0000"
"TLMLV+5605.0000+ 0.0000"
"TLMLV+5606.0000+ 75.0000"
"TLMLV+5607.0000+ 175.0000"
April 24, 2008 at 9:20 am
Hi,
Is this the sort of thing that you're trying to achieve?
DECLARE @table TABLE (raw_payload VARCHAR(200))
INSERT INTO @table VALUES('UV+ 142.0000+ 0.0000')
INSERT INTO @table VALUES('UVN+ 524.0000+ 0.0000')
INSERT INTO @table VALUES('UVN+ 525.0000+ 0.0000')
INSERT INTO @table VALUES('UVN+ 527.0000+ 0.0000')
INSERT INTO @table VALUES('UVN+ 528.0000+ 0.0000')
INSERT INTO @table VALUES('TLOV+2044.0000+ 0.0000')
INSERT INTO @table VALUES('TLOV+2045.0000+ 0.0000')
INSERT INTO @table VALUES('OTD/ROV+2446.0000+ 0.0000')
INSERT INTO @table VALUES('G59WO+5324.0000+ 36.6190')
INSERT INTO @table VALUES('TLMLV+5601.0000+ 0.0000')
SELECT LEFT(raw_payload, CHARINDEX('+', raw_payload, 1)-1),
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, 1) + 1,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) - (CHARINDEX('+', raw_payload, 1) + 1))
AS FLOAT),
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) + 1,
LEN(raw_payload) - CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) + 1)
AS FLOAT)
FROM @table
HTH, 😀
April 24, 2008 at 10:00 am
The Final result I want is the column or string... UV+100.000+23.000
Description USAGE ADJUSTABLES
UV 100 23.0000
TMLN 101 480.0000
into a new table with three columns...
April 24, 2008 at 10:23 am
April 24, 2008 at 10:29 am
Call me SQL stupid because I am, but its the small adjustments that are confusing me...Sorry
I ran your query and it came back with one row effected. but displayed nothing...
I will keep playing with it.
Thanks for your help I have been trying to figure this out for a while now...
John
April 24, 2008 at 10:36 am
Me again!
When I run the entire script I get:
UV 142.0 0.0
UVN 524.0 0.0
UVN 525.0 0.0
UVN 527.0 0.0
UVN 528.0 0.0
TLOV 2044.0 0.0
TLOV 2045.0 0.0
OTD/ROV 2446.0 0.0
G59WO 5324.0 36.619
TLMLV 5601.0 0.0
:crazy:
April 24, 2008 at 10:45 am
Ok, I got it to display the way you said, but I think the issue is me not explaining properly,
I have a database with a table called raw_data and a field called raw_payload that collects 30000 an hour of data in the format I posted with the + delimiters and I dont want to seperate just that hand full sample but all data that is populated into the data base field raw_payload 3 times a day based on 8 hour periods of time....
April 24, 2008 at 1:58 pm
The code I provided is an example of how it works (using a small amount of data. You need to change the table that the select works on and also turn it into an INSERT INTO myTable SELECT to perform the insert. Put that in to a procedure and have an Agent job exeute that procedure whenever you need it to.
HTH
April 25, 2008 at 6:31 am
Tried this
DECLARE @table TABLE (raw_payload VARCHAR(200))
INSERT INTO @Table SELECT raw_payload FROM raw_data
SELECT LEFT(raw_payload, CHARINDEX('+', raw_payload, 1)-1),
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, 1) + 1,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) - (CHARINDEX('+', raw_payload, 1) + 1))
AS FLOAT),
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) + 1,
LEN(raw_payload) - CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) + 1)
AS FLOAT)
FROM @table
This is the result...
(2536030 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 5
Invalid length parameter passed to the substring function.
April 25, 2008 at 6:37 am
My first thought is, are there any rows in the raw_data table where the raw_payload column doesn't contain a comma, or are NULL?
SELECT raw_payload
FROM raw_data
WHERE raw_payload IS NULL
OR CHARINDEX(',', raw_payload, 1) = 0
? :ermm:
April 25, 2008 at 6:44 am
YES, some null and some stepped on data, its rs232 data coming over ethernet out of 30000 rows there maybe 20 bad and i am trying to purge them any ways... the bad not the null... The more automatic the better. Currently I export SQL data to access export format in excel reimport into access do some calculations re export into excel massage some more re import into access and print reports.. all with macros the process is long to say the least, this separation in SQL would cut this process time in half.
Thanks for all your help I am a systems admin guy Not a SQL guy...
John
April 25, 2008 at 6:49 am
Hey no problem. During one contract a few years ago, that was all Excel and Access... Can't say I'm sad not to be working on that any more!! :hehe:
I think that if you add a WHERE clause to the SELECT, something like the one I used in my previous post, that'll trim out all of the 'bad' data. You can then have another query to output the bad data separately so you only have to deal with that?
Or am I missing something?
April 25, 2008 at 6:54 am
No your clear and I am the missing something in the statement where would you add the null and or no delimiter statement....
April 25, 2008 at 7:03 am
Adrian... take a look at the original post... the first entry in the data looks like 'S2009+' which means that you'll get invalid substring messages for the 2 and 3rd split.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2008 at 7:28 am
This one will handle the data even if one or more of the rows is incomplete:
create table #Raw (
ID int identity primary key,
RawData varchar(100))
insert into #Raw (rawdata)
select parsed
from common.dbo.stringparserxml('"S9000+"
"UV+ 131.0000+ 0.0000"
"UV+ 132.0000+ 0.0000"
"UV+ 140.0000+ 0.0000"
"UV+ 141.0000+ 0.0000"
"UV+ 142.0000+ 0.0000"
"UVN+ 524.0000+ 0.0000"
"UVN+ 525.0000+ 0.0000"
"UVN+ 527.0000+ 0.0000"
"UVN+ 528.0000+ 0.0000
"TLOV+2044.0000+ 0.0000"
"TLOV+2045.0000+ 0.0000"
"TLOV+2046.0000+ 0.0000"
"TLOV+2047.0000+ 0.0000"
"TLOV+2048.0000+ 0.0000"
"TLOV+2049.0000+ 0.0000"
"OTLWV+2201.0000+ 0.0000"
"OTLWV+2202.0000+ 0.0000"
"OTD/ROV+2401.0000+ 0.0000"
"OTD/ROV+2402.0000+ 0.0000"
"OTD/ROV+2403.0000+ 0.0000"
"OTD/ROV+2404.0000+ 0.0000"
""OTD/ROV+2432.0000+ 0.0000"
"OTD/ROV+2436.0000+ 0.0000"
"OTD/ROV+2443.0000+ 0.0000"
"OTD/ROV+2444.0000+ 0.0000"
"OTD/ROV+2445.0000+ 0.0000"
"OTD/ROV+2446.0000+ 0.0000"
"G59WO+5324.0000+ 36.6190"
"TLMLV+5601.0000+ 0.0000"
"TLMLV+5602.0000+ 20.0000"
"TLMLV+5603.0000+ 0.0000"
"TLMLV+5605.0000+ 0.0000"
"TLMLV+5606.0000+ 75.0000"
"TLMLV+5607.0000+ 175.0000"','
')
update #raw
set rawdata = ltrim(rtrim(replace(rawdata, '"', '')))
set statistics io on
set statistics time on
;with CTE (ID, Row, Parsed) as
(select id, row, parsed
from #raw
cross apply common.dbo.StringParserXML(rawdata, '+'))
select id,
(select parsed
from CTE col1
where row = 1
and id = cte.id),
(select parsed
from CTE col2
where row = 2
and id = cte.id),
(select parsed
from CTE col2
where row = 3
and id = cte.id)
from CTE
order by id
You'll need this for it to work:
create database Common
go
use Common
go
create function [dbo].[StringParserXML]
(@String_in varchar(max),
@Delimiter_in char(10))
returns @Parsed table (
Row int,
Parsed varchar(100))
as
-- This one is faster than Numbers table, but it doesn't correctly handle
-- XML-specific characters, such as "<" or "&". Numbers table version will handle those
-- without difficulty.
begin
if right(@string_in, 1) = @delimiter_in
select @string_in = left(@string_in, len(@string_in) - 1)
declare @XML xml
select @xml = ' '
insert into @parsed(row, parsed)
select row_number() over (order by x.i.value('.', 'varchar(100)')), x.i.value('.', 'varchar(100)')
from @xml.nodes('//i') x(i)
return
end
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply