SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extracting Rows from Delimited Strings


Extracting Rows from Delimited Strings

Author
Message
n1pcman
n1pcman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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"
Adrian Nichols-360275
Adrian Nichols-360275
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 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, BigGrin



Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
n1pcman
n1pcman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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...
Adrian Nichols-360275
Adrian Nichols-360275
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 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...
n1pcman
n1pcman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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
Adrian Nichols-360275
Adrian Nichols-360275
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 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

Crazy



Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
n1pcman
n1pcman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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....
Adrian Nichols-360275
Adrian Nichols-360275
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 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...
n1pcman
n1pcman
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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.

Adrian Nichols-360275
Adrian Nichols-360275
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 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

? Ermm



Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search