Cleaning - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Cleaning

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-07 : 21:43:13

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

sshelper
Posting Yak Master

216 Posts

Posted - 2012-08-08 : 00:53:44

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

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-08-08 : 01:21:16

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 02:04:49

Thanks SShelper & Flamblaster
PARSENAME instead of Substring?

-Neil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-08 : 03:04:48

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources