SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cleaning		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aakcse
Aged Yak Warrior

India
517 Posts
Posted - 08/07/2012 :  21:43:13  Show Profile  Reply with Quote
I want to clean the below data, which is space delimited, here I want to put this data into 3 columns, 1st & 2nd col will have the first & second delimited data and rest all data I want to put it in 3rd column irrespective of any data it should go to 3rd col

The below data is in single column raw with Id as identity say Table1(id,raw)

Id Raw
1  $.debug /vary/log/slog/slog.log rotate size 100m files 4 compress
2  $.info /vary/admin/ras/slog.caa rotate size 1m files 10
3  authinfo /var/log/authlog


cleaned file will be as 


Col1              Col2                              Col3
$.debug           /vary/log/slog/slog.log           rotate size 100m files 4 compress
$.info            /vary/admin/ras/slog.caa          rotate size 1m files 10
authinfo          /var/log/authlog





-Neil
Edited by - aakcse on 08/07/2012 21:45:32

sshelper
Posting Yak Master

213 Posts
Posted - 08/08/2012 :  00:53:44  Show Profile  Visit sshelper's Homepage  Reply with Quote
Try this query to separate your space-delimited string into 3 columns:

DECLARE @RAW VARCHAR(1000)
SET @RAW = '$.debug /vary/log/slog/slog.log rotate size 100m files 4 compress'

SELECT LEFT(@Raw, CHARINDEX(' ', @Raw) - 1) AS [Col1],
SUBSTRING(@Raw, CHARINDEX(' ', @Raw) + 1, CHARINDEX(' ', @Raw, CHARINDEX(' ', @Raw) + 1) - CHARINDEX(' ', @Raw) - 1) AS [Col2],
SUBSTRING(@Raw, CHARINDEX(' ', @Raw, CHARINDEX(' ', @Raw) + 1) + 1, LEN(@Raw)) AS [Col3]

Regards,
SQL Server Helper
http://www.sql-server-helper.com/tips/tip-of-the-day.aspx
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

USA
355 Posts
Posted - 08/08/2012 :  01:21:16  Show Profile  Click to see flamblaster's MSN Messenger address  Reply with Quote
You could also use PARSENAME since it's only 3 columns. Still figuring out column 3, but here's a start:

select parsename(raw,1) as ColA,
COALESCE(parsename(raw,2),'') as ColB

from YourTable
Go to Top of Page

aakcse
Aged Yak Warrior

India
517 Posts
Posted - 08/08/2012 :  02:04:49  Show Profile  Reply with Quote
Thanks SShelper & Flamblaster
PARSENAME instead of Substring?

-Neil
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/08/2012 :  03:04:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, I think flambaster is joking.
PARSENAME split the string where there are dots in the text, not spaces.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000