January 10, 2012 at 11:12 am
I have a string that looks like the following:
20110101,glp,100.0,50.0,25.6 hgl,29.0,28.0 jlp, 30.0
how can i split via space and comma so i have the desired output into a sql table.
20110101 glp 100.0 50.0 25.6
20110101 hgl 29.0 28.0
20110101 jlp 30.0
January 10, 2012 at 12:34 pm
js_0505 (1/10/2012)
I have a string that looks like the following:20110101,glp,100.0,50.0,25.6 hgl,29.0,28.0 jlp, 30.0
how can i split via space and comma so i have the desired output into a sql table.
20110101 glp 100.0 50.0 25.6
20110101 hgl 29.0 28.0
20110101 jlp 30.0
Wow that is particularly nasty. The format of your example is not consistent across "rows". You have a space in 'jlp, 30.0'.
You can probably brute force through much trial and error (using the string splitter you will find a link to in my signature). You are going to put this data into a couple temp tables to pull this off. First you will have to replace ', ' with ','. Then you will have to split on space. Then remove the repeating data "20110101" and split the results on ','.
If at ALL possible, can you get your source in a clean and normalized format instead of this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2012 at 12:39 pm
What's the definition of the destination table?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 12:46 pm
Currently I have not developed the destination table.
January 10, 2012 at 12:47 pm
Currently I am utilizing replace which is quite nasty because I can not make it universal, I am pulling this via a web crawler I created via ssis.
January 10, 2012 at 12:51 pm
This might be a start...
declare @string varchar(100) = '20110101,glp,100.0,50.0,25.6 hgl,29.0,28.0 jlp, 30.0'
set @string = REPLACE(@string, ', ', ',')
select *
into #Results
from dbo.DelimitedSplit8K(@string, ' ')
--validate the initial load
--select * from #Results
declare @Prefix varchar(10)
--first you need to get the "prefix"
select @Prefix = SUBSTRING(Item, 0, CHARINDEX(',', Item))
from #Results where ItemNumber = 1
--validate the prefix
--select @Prefix as prefix
--remove the prefix from the first line
update #Results set Item = SUBSTRING(Item, CHARINDEX(',', Item) + 1, DATALENGTH(Item) - CHARINDEX(',', Item))
where ItemNumber = 1
select @Prefix as Prefix, Item
from #Results r
drop table #Results
This is utilizing the splitter I referred to in my first post. If you need the second column to be split apart that will take a dynamic cross tab. Jeff Moden has two great article on cross tabs.
http://qa.sqlservercentral.com/articles/T-SQL/63681/[/url]
http://qa.sqlservercentral.com/articles/Crosstab/65048/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2012 at 1:33 pm
Thanks, I will see if I can work with that. Your assistance is greatly appreciated. For some reason though I guess I have some spaces that are not technically spaces because the replace does not work on the true dataset fully. Here is an example of the full text string.
20020101 , , , , , , , , , , , , , , , , , , , , , , , , , GLP,2.930000,2.920000,2.880000,2.850000,2.820000,2.830000,2.870000,2.890000,2.880000,2.910000,2.940000,3.140000,3.160000,3.170000,3.120000,3.120000,3.220000,3.490000,3.460000,3.530000,3.430000,3.180000,3.010000,2.970000,0.000000 HS ,3.370000,3.380000,3.530000,3.760000,3.790000,4.020000,4.150000,4.210000,4.010000,3.850000,3.690000,3.320000,3.140000,3.040000,2.960000,2.970000,3.060000,3.070000,3.110000,3.140000,3.020000,3.100000,3.140000,3.220000,0.000000 LPL,164.240000,164.010000,164.790000,168.300000,169.140000,170.050000,173.940000,174.100000,173.600000,173.590000,172.410000,171.180000,169.500000,168.860000,168.630000,167.880000,171.730000,177.340000,174.350000,172.820000,169.680000,168.980000,169.270000,167.710000,0.000000 RHS,3.250000,2.920000,2.960000,2.930000,2.910000,3.110000,3.210000,3.590000,3.730000,3.730000,3.740000,3.710000,3.330000,3.540000,3.170000,3.330000,3.580000,3.670000,4.140000,3.920000,3.640000,3.340000,3.490000,2.770000,0.000000 RLM,2.480000,2.270000,1.920000,1.830000,1.770000,1.650000,1.730000,1.750000,1.760000,1.850000,1.880000,1.940000,1.910000,2.060000,2.160000,2.260000,2.470000,2.900000,3.190000,3.210000,3.170000,3.200000,2.880000,2.400000,0.000000 RS ,0.930000,0.860000,0.750000,0.680000,0.670000,0.680000,0.610000,0.660000,0.670000,0.720000,0.780000,0.860000,0.870000,0.780000,0.850000,0.880000,1.070000,1.360000,1.330000,1.270000,1.320000,1.210000,0.990000,0.830000,0.000000 WH ,0.420000,0.420000,0.210000,0.160000,0.130000,0.140000,0.240000,0.200000,0.220000,0.330000,0.280000,0.470000,0.380000,0.350000,0.290000,0.250000,0.280000,0.260000,0.240000,0.280000,0.240000,0.230000,0.170000,0.750000,0.000000 WHS,0.900000,0.230000,0.360000,0.170000,0.200000,0.060000,0.050000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,2.130000,1.150000,0.640000,0.000000
January 10, 2012 at 1:41 pm
disregard the last question, i have found that they are char(9) and i used replace for that, thank you again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply