InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 08:45:10 GMT20http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspx[quote][b]n1pcman (4/29/2008)[/b][hr]That worked Thanks All, Now one more missing peice...I copy good data into a new table then I run the parsing query which seperates my data the way I want it now I want to Insert Into a new table with 3 fields"Description Field would be Text" a "Usage Field would be a Number or decimal ??? " and a "Adjustable field would be a Number or decimal ???"SAMPLE OF DATA IN THREE (NO COLUMN NAMES)UV 100.0000 101.0000TLMV 5620.0000 -25.0000[/quote]Based on the errors you've been reporting ("Error converting varchar to float") there is at least an instance somewhere of either the usage or the adjustable column not holding numeric data (meaning - it's alphanumeric). Try setting just the usage field to be numeric, and try your insert. If it fails, then it's not holding only numeric data, and you can go find out what you need to get rid of (apparently more "bad data").Tue, 29 Apr 2008 09:56:31 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThat worked Thanks All, Now one more missing peice...I copy good data into a new table then I run the parsing query which seperates my data the way I want it now I want to Insert Into a new table with 3 fields"Description Field would be Text" a "Usage Field would be a Number or decimal ??? " and a "Adjustable field would be a Number or decimal ???"SAMPLE OF DATA IN THREE (NO COLUMN NAMES)UV 100.0000 101.0000TLMV 5620.0000 -25.0000Tue, 29 Apr 2008 08:37:45 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThen either your column # 2 or column # 3 is returning something that can't be cast as a float. Meaning - it's not a number.So take the CAST out, and see if it flies:[code]SELECT raw_payload, LEFT(raw_payload+'+++', CHARINDEX('+', raw_payload+'+++', 1)-1), NULLIF( SUBSTRING(raw_payload+'+++', CHARINDEX('+', raw_payload+'+++', 1) + 1, CHARINDEX('+', raw_payload+'+++', CHARINDEX('+', raw_payload, 1) +1) - (CHARINDEX('+', raw_payload+'+++', 1) + 1)) ,'') --<---CHANGE THIS LINE , 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) ,'+','') ,'') --<---CHANGE THIS LINEFROM raw_data_pro[/code]Tue, 29 Apr 2008 08:11:40 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxServer: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to float.New Error after uncommentedTue, 29 Apr 2008 07:49:13 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxYou uncommented the "beginning" of the NULLIF, but not the end:[code]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)) ,'') --<---CHANGE THIS LINE 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) ,'+','') ,'') --<---CHANGE THIS LINE AS FLOAT)FROM raw_data_pro[/code]You would then need to read through and see what is not returning a numeric value in column 2 or 3, and somehow handle that.Tue, 29 Apr 2008 07:44:31 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxINSERT INTO dbo.raw_data_proSelect * FROM raw_data[raw_payload]Where raw_payload like '%+%+%' This works but the parsing does not....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_proERRORServer: Msg 156, Level 15, State 1, Line 10Incorrect syntax near the keyword 'AS'.Tue, 29 Apr 2008 07:38:16 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxWHEN I RUN THIS:INSERT INTO dbo.raw_data_proVALUES ('raw_seq_no','raw_date','raw_dev_id','raw_rec_type','raw_payload','raw_processed')Select * FROM raw_data[raw_payload]Where raw_payload like '%+%+%'I GET THIS ERROR...Server: Msg 8152, Level 16, State 9, Line 1String or binary data would be truncated.The statement has been terminated.(2898292 row(s) affected) Tue, 29 Apr 2008 06:48:16 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxIf you want to just delete those rows - then [code]DELETEFROM raw_data[raw_payload]Where raw_payload not like '%+%+%'[/code]Tue, 29 Apr 2008 06:24:21 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxSelect * FROM raw_data[raw_payload]Where raw_payload not like '%+%+%'This finds all the bad data is there some thing I can add to auto delete based on this query like a stored procedure or something and then run the parser on clean data all on a scheduled cycle??? or all the good data with thisSelect * FROM raw_data[raw_payload]Where raw_payload like '%+%+%'Tue, 29 Apr 2008 06:14:01 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxI dont know enough about SQL to any of this I just know what I want, the bad data or non standard data can be trashed with no problems.Mon, 28 Apr 2008 12:51:37 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspx[quote][b]n1pcman (4/25/2008)[/b][hr]Yes that is the problem I do have some bad data, 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.[/quote]Then - try one of the above methods, and dump the data into varchar(20) fields FIRST. Then figure out if what you got in those fields is convertible to FLOAT: if it is - keep it - if not - chunk it. As of now - the parsing is working, it's just that it's parsing non-numbers in a couple of records.Mon, 28 Apr 2008 12:45:07 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxWhat if we could write a query that says pickup all data that meets this standard text(*) '+' NUM(*) '+' NUM(*) copy to new table then parse into a new table with 3 fields"Description Field would be Text" & "Usage Field would be a Number" & "Adjustable field would be a Number"Mon, 28 Apr 2008 12:39:06 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspx[quote][b]Matt Miller (4/25/2008)[/b][hr]Jeff - that last code nulls out the 0's in the 3rd column - is it supposed to?[/quote]Nah... I screwed up.The real key to this whole thing is that the data must be "equalized". That is, every row should be updated to have the same number of delimiters before any splitting is attempted. Sure, we can hack all day with CHARINDEX and the like, but if the original data is "equalized", this becomes a simple split and reassemble problem.Fri, 25 Apr 2008 20:17:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxYes that is the problem I do have some bad data, 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.Fri, 25 Apr 2008 09:59:49 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspx[quote][b]n1pcman (4/25/2008)[/b][hr]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_dataRESULT:Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to float.[/quote]hmm - it works for me as long as the second and third parameters are numeric or missing. Are you sure you don't have any of those that are NOT numeric?Perhaps try commenting out one of the casts and then the other, just to find out which one is causing the headache.Fri, 25 Apr 2008 09:52:33 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxSELECT 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_dataRESULT:Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to float.Fri, 25 Apr 2008 09:38:39 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspx[quote][b]Jeff Moden (4/25/2008)[/b][hr]Gus,This is an SQL Server 2000 forum... ;)[/quote]Well, that'll certainly change a few things! (Like maybe I'll start paying attention.... nah ... that'll never happen.)For that, you'll need a numbers-based string parser (there's one on the scripts section of SQL Server Central), instead of the XML version.Also, would need to use regular derived tables in the From clause, instead of a CTE. Should work with those modifications.Fri, 25 Apr 2008 09:16:50 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxAll right - one more variation on Adrian's original solution, avoiding the need for the CASE statements....[code][font="Courier New"][size="2"][color="blue"]SELECT        [/color][color="magenta"]LEFT[/color][color="gray"]([/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"], [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"], [/color][color="black"]1[/color][color="gray"])-[/color][color="black"]1[/color][color="gray"]),        [/color][color="magenta"]CAST[/color][color="gray"]([/color][color="green"]--NULLIF(               [/color][color="magenta"]SUBSTRING[/color][color="gray"]([/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"],                    [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"], [/color][color="black"]1[/color][color="gray"]) + [/color][color="black"]1[/color][color="gray"],                        [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"],                            [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"], [/color][color="black"]1[/color][color="gray"]) +[/color][color="black"]1[/color][color="gray"]) -                                ([/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"], [/color][color="black"]1[/color][color="gray"]) + [/color][color="black"]1[/color][color="gray"]))               [/color][color="green"]--,'')                [/color][color="blue"]AS FLOAT[/color][color="gray"]),       [/color][color="magenta"]CAST[/color][color="gray"]([/color][color="green"]--NULLIF(               [/color][color="magenta"]REPLACE[/color][color="gray"](                   [/color][color="magenta"]SUBSTRING[/color][color="gray"]([/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"],                        [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"],                            [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"], [/color][color="black"]1[/color][color="gray"]) +[/color][color="black"]1[/color][color="gray"]) + [/color][color="black"]1[/color][color="gray"],                                [/color][color="magenta"]LEN[/color][color="gray"]([/color][color="black"]raw_payload[/color][color="gray"]) - [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"],                                [/color][color="blue"]CHARINDEX[/color][color="gray"]([/color][color="red"]'+'[/color][color="gray"], [/color][color="black"]raw_payload[/color][color="gray"]+[/color][color="red"]'+++'[/color][color="gray"], [/color][color="black"]1[/color][color="gray"]) +[/color][color="black"]1[/color][color="gray"]) + [/color][color="black"]1[/color][color="gray"])                    ,[/color][color="red"]'+'[/color][color="gray"],[/color][color="red"]''[/color][color="gray"])               [/color][color="green"]--,'')            [/color][color="blue"]AS FLOAT[/color][color="gray"])[/color][color="blue"]FROM [/color][color="#434343"]#mytable[/color][/size][/font][/code]Now - I'm not crazy that the NULL columns are being converted to 0 (so you can't tell what was passed in as 0 versus no input), so you might want the NULLIF's uncommmented, but they're fairly costly (they double processing time).Perf-wise - it looks like:Jeff's: 5,907msThis one (without NULLIF): 7,015msThis one (with NULLIF): 11,536msI still haven't quite figured out where the flaw is in Jeff's that ends up nulling out the third column.Fri, 25 Apr 2008 09:06:49 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxJeff - that last code nulls out the 0's in the 3rd column - is it supposed to?Fri, 25 Apr 2008 08:43:31 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxCODE PASSED:DECLARE @table TABLE (raw_payload VARCHAR(200))INSERT INTO @Table SELECT raw_payload FROM raw_dataSELECT 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 ENDRESULTS:(2551671 row(s) affected)(624 row(s) affected)Server: Msg 536, Level 16, State 3, Line 5Invalid length parameter passed to the substring function.SAMPLE RESULTS:S9000 NULL NULLUV 100.0 NULLUV 101.0 NULLUV 102.0 NULLUV 103.0 NULLUV 104.0 NULLThird column is not null in these cases but that is what is diplayed.Fri, 25 Apr 2008 08:09:59 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxTry this... it's Adrian's original code with some conditions added...[code]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 ENDFROM #SplitMe[/code]Fri, 25 Apr 2008 07:41:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThanks Jeff, I'd missed that completely.:blush:Is there a better way than just adding CASE statments to filter these correctly?Ade.Fri, 25 Apr 2008 07:32:31 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxGus,This is an SQL Server 2000 forum... ;)Fri, 25 Apr 2008 07:29:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThis one will handle the data even if one or more of the rows is incomplete:[code]create table #Raw (ID int identity primary key,RawData varchar(100))insert into #Raw (rawdata)select parsedfrom 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 #rawset rawdata = ltrim(rtrim(replace(rawdata, '"', '')))set statistics io onset 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 CTEorder by id[/code]You'll need this for it to work:[code]create database Commongouse Commongocreate 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)returnend[/code]Fri, 25 Apr 2008 07:28:13 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxAdrian... 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.Fri, 25 Apr 2008 07:03:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxNo your clear and I am the missing something in the statement where would you add the null and or no delimiter statement....Fri, 25 Apr 2008 06:54:09 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxHey 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?Fri, 25 Apr 2008 06:49:43 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxYES, 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...JohnFri, 25 Apr 2008 06:44:17 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxMy 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_payloadFROM raw_dataWHERE raw_payload IS NULLOR CHARINDEX(',', raw_payload, 1) = 0? :ermm:Fri, 25 Apr 2008 06:37:05 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspx[b]Tried this[/b]DECLARE @table TABLE (raw_payload VARCHAR(200))INSERT INTO @Table SELECT raw_payload FROM raw_dataSELECT 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[b]This is the result...(2536030 row(s) affected)Server: Msg 536, Level 16, State 3, Line 5Invalid length parameter passed to the substring function.[/b]Fri, 25 Apr 2008 06:31:22 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThe 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.HTHThu, 24 Apr 2008 13:58:46 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxOk, 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....Thu, 24 Apr 2008 10:45:33 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxMe 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:Thu, 24 Apr 2008 10:36:30 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxCall me SQL stupid because I am, but its the small adjustments that are confusing me...SorryI 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...JohnThu, 24 Apr 2008 10:29:18 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThat's what the query above should do for you (not in to a table mind you, but it's an easy adjustment to make)Thu, 24 Apr 2008 10:23:36 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThe Final result I want is the column or string... UV+100.000+23.000Description USAGE ADJUSTABLESUV 100 23.0000TMLN 101 480.0000into a new table with three columns...Thu, 24 Apr 2008 10:00:54 GMTn1pcmanhttp://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxHi,Is this the sort of thing that you're trying to achieve?[code]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[/code]HTH, :DThu, 24 Apr 2008 09:20:16 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic489866-169-1.aspxThe query I am passing the two letter strings like "[b]UV[/b]+ 131.0000+ 0.0000" work fine when it goes to 3 and up like "[b]TLOV[/b]+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.dboFieldname: 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"Thu, 24 Apr 2008 06:46:15 GMTn1pcman