May 5, 2016 at 1:18 am
Hello,
i have one column having data like
777777777777777777 0 0 0 0 0 0 0 16090 0 0 0 0 0 0
The space between two value is not same for each record and column
I want to split into 15 diffrent columns.
May 5, 2016 at 1:58 am
May 5, 2016 at 2:56 am
Its for single record I have 10 million records in table.
and space betwwen two values is not fixed .
somewhre its 3 to 10
May 5, 2016 at 4:06 am
Nallya (5/5/2016)
Its for single record I have 10 million records in table.and space betwwen two values is not fixed .
somewhre its 3 to 10
please see this article that shows how to remove multiple spaces
http://www.sqlservercentral.com/articles/T-SQL/68378/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 5, 2016 at 6:29 am
Nallya (5/5/2016)
Hello,i have one column having data like
777777777777777777 0 0 0 0 0 0 0 16090 0 0 0 0 0 0
The space between two value is not same for each record and column
I want to split into 15 diffrent columns.
Is the length of the string always the same? Is the space dependent on the length of each value? If so, you might be dealing with fixed length columns which are better divided using SUBSTRING().
If not, I'd use a different splitter that will treat all spaces as one. You can find it in here: http://www.sqlservercentral.com/articles/String+Manipulation/94365/
DECLARE @T TABLE(
LongString varchar(8000)
)
INSERT INTO @T
VALUES( '777777777777777777 0 0 0 0 0 0 0 16090 0 0 0 0 0 0');
SELECT
MAX( CASE WHEN ItemNumber = 1 THEN Item END) AS Col01,
MAX( CASE WHEN ItemNumber = 3 THEN Item END) AS Col02,
MAX( CASE WHEN ItemNumber = 5 THEN Item END) AS Col03,
MAX( CASE WHEN ItemNumber = 7 THEN Item END) AS Col04,
MAX( CASE WHEN ItemNumber = 9 THEN Item END) AS Col05,
MAX( CASE WHEN ItemNumber = 11 THEN Item END) AS Col06,
MAX( CASE WHEN ItemNumber = 13 THEN Item END) AS Col07,
MAX( CASE WHEN ItemNumber = 15 THEN Item END) AS Col08,
MAX( CASE WHEN ItemNumber = 17 THEN Item END) AS Col09,
MAX( CASE WHEN ItemNumber = 19 THEN Item END) AS Col10,
MAX( CASE WHEN ItemNumber = 21 THEN Item END) AS Col11,
MAX( CASE WHEN ItemNumber = 23 THEN Item END) AS Col12,
MAX( CASE WHEN ItemNumber = 25 THEN Item END) AS Col13,
MAX( CASE WHEN ItemNumber = 27 THEN Item END) AS Col14,
MAX( CASE WHEN ItemNumber = 29 THEN Item END) AS Col15
FROM @T
CROSS APPLY dbo.PatternSplitCM( LongString, ' ')
WHERE Matched = 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply