Extracting Rows from Delimited Strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Extracting Rows from Delimited Strings

Author

Topic

n1coltsfan
Starting Member

2 Posts

Posted - 2008-04-25 : 14:36:32

1 or 2 lines per every 600 is stepped on data its un unavoidable buffering issue with the device dumping the data. I don't want that data if I do this process manually I delete it. I am just hoping to come up with a stored proc or something that cleans the data for my queries and reports. I want the first row text, second row number and third row number in the finished deal.
Small sample of raw data + sign is my delimiter,

"UVN+ 690.0000+ 0.0000"
"UVN+ 691.0000+ 5.0000"
"UVN+ 692.0000+ 0.0000"
"UVN+ 693.0000+ 5.4500"
"UVN+ 694.0000+ 0.0000"
"UVN+ 695.0000+ 0.0000"
"UVN+ 696.0000+ 0.0000"
"UVN+ 697.0000+ 0.0000"
"UVN+ 698.0000+ 0.0000"
"TLOV+2001.0000+ 0.0000"
"TLOV+2002.0000+ 94.9660"
"TLOV+2003.0000+ 0.0000"
"TLOV+2004.0000+ 233.2690"

Here is the code I am using below is the result it works if row 2 & 3 are numbers or null but not if junk data which is unavoidable,

SELECT raw_payload,
LEFT(raw_payload+'+++', CHARINDEX('+', raw_payload+'+++', 1)-1),
CAST(--NULLIF(
SUBSTRING(raw_payload+'+++',
CHARINDEX('+', raw_payload+'+++', 1) + 1,
CHARINDEX('+', raw_payload+'+++',
CHARINDEX('+', raw_payload, 1) +1) -
(CHARINDEX('+', raw_payload+'+++', 1) + 1))
--,'')
AS FLOAT),
CAST(--NULLIF(
REPLACE(
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 raw_data

RESULT:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 14:44:31

why do you have '+++' everywhere? and do you really mean row or do you mean column?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-04-25 : 14:55:01

what's an example of the "stepped on" data?

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources