Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Extracting Rows from Delimited Strings
Add to briefcase
38 posts, Page 1 of 4
1234»»»

Extracting Rows from Delimited Strings Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
n1pcman
Posted Thursday, April 24, 2008 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
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"
Post #489866
Adrian Nichols-360275
Posted Thursday, April 24, 2008 9:20 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 04, 2009 10:02 AM
Points: 814, Visits: 915
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, :D


Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #490036
n1pcman
Posted Thursday, April 24, 2008 10:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
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...
Post #490075
Adrian Nichols-360275
Posted Thursday, April 24, 2008 10:23 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 04, 2009 10:02 AM
Points: 814, Visits: 915
That's what the query above should do for you (not in to a table mind you, but it's an easy adjustment to make)


Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #490091
n1pcman
Posted Thursday, April 24, 2008 10:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
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
Post #490097
Adrian Nichols-360275
Posted Thursday, April 24, 2008 10:36 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 04, 2009 10:02 AM
Points: 814, Visits: 915
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




Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #490104
n1pcman
Posted Thursday, April 24, 2008 10:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
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....
Post #490112
Adrian Nichols-360275
Posted Thursday, April 24, 2008 1:58 PM


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 04, 2009 10:02 AM
Points: 814, Visits: 915
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


Ade

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 5:40 AM
Points: 17, Visits: 28
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.
Post #490582
Adrian Nichols-360275
Posted Friday, April 25, 2008 6:37 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 04, 2009 10:02 AM
Points: 814, Visits: 915
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

?


Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #490589
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.