Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Extracting Rows from Delimited Strings Expand / Collapse
Author
Message
Posted Friday, April 25, 2008 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
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
Post #490597
Posted Friday, April 25, 2008 6:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 810, Visits: 915
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!!

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?




Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #490601
Posted Friday, April 25, 2008 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
No your clear and I am the missing something in the statement where would you add the null and or no delimiter statement....
Post #490606
Posted Friday, April 25, 2008 7:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #490619
Posted Friday, April 25, 2008 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #490645
Posted Friday, April 25, 2008 7:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
Gus,

This is an SQL Server 2000 forum... ;)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #490648
Posted Friday, April 25, 2008 7:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 810, Visits: 915
Thanks Jeff, I'd missed that completely.

Is there a better way than just adding CASE statments to filter these correctly?

Ade.




Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #490650
Posted Friday, April 25, 2008 7:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
Try this... it's Adrian's original code with some conditions added...

SELECT         LEFT(raw_payload, CHARINDEX('+', raw_payload, 1)-1),
CASE
WHEN LEN(raw_payload)-LEN(REPLACE(raw_payload,'+','')) >= 2
THEN
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, 1) + 1,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) - (CHARINDEX('+', raw_payload, 1) + 1))
AS FLOAT)
ELSE NULL END,
CASE
WHEN LEN(raw_payload)-LEN(REPLACE(raw_payload,'+','')) >= 3
THEN
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)
ELSE NULL END
FROM #SplitMe



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #490663
Posted Friday, April 25, 2008 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
CODE PASSED:

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),
CASE
WHEN LEN(raw_payload)-LEN(REPLACE(raw_payload,'+','')) >= 2
THEN
CAST(SUBSTRING(raw_payload,
CHARINDEX('+', raw_payload, 1) + 1,
CHARINDEX('+', raw_payload, CHARINDEX('+', raw_payload, 1) +1) - (CHARINDEX('+', raw_payload, 1) + 1))
AS FLOAT)
ELSE NULL END,
CASE
WHEN LEN(raw_payload)-LEN(REPLACE(raw_payload,'+','')) >= 3
THEN
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)
ELSE NULL END

RESULTS:

(2551671 row(s) affected)

(624 row(s) affected)

Server: Msg 536, Level 16, State 3, Line 5
Invalid length parameter passed to the substring function.

SAMPLE RESULTS:

S9000 NULL NULL
UV 100.0 NULL
UV 101.0 NULL
UV 102.0 NULL
UV 103.0 NULL
UV 104.0 NULL

Third column is not null in these cases but that is what is diplayed.
Post #490686
Posted Friday, April 25, 2008 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 7,064, Visits: 15,270
Jeff - that last code nulls out the 0's in the 3rd column - is it supposed to?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #490709
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse