Separate Data in multi column

Question

Can any one help me to fetch separate data from database into multi column. how to build this query in sql server 2008. i have data like 1003,1253569585,05/05/2020 10:25:13 i want to fetch this data in sepatare filed and if emp in time sec is odd then status is 'IN' and time sec is even then 'Out' please help me in this query...

Kamil Krupski · Answer

There are many ways to get these values out. You can try this: declare @input varchar (100) = '1003,1253569585,05/05/2020 10:25:13' SELECT LEFT (@input, CHARINDEX( ',' ,@input)-1) [Id], LEFT ( SUBSTRING (@input, CHARINDEX( ',' ,@input) + 1, LEN(@input) - CHARINDEX( ',' ,@input)), CHARINDEX( ',' , SUBSTRING (@input, CHARINDEX( ',' ,@input) + 1, LEN(@input) - CHARINDEX( ',' ,@input))) - 1) [SomeInt], cast ( RIGHT (@input, CHARINDEX( ',' , REVERSE(@input)) - 1) as datetime) [ Date ], CASE WHEN cast ( RIGHT (@input, 1) as int ) % 2 = 0 THEN 'out' else 'in' end [Mod]

Kamil Krupski · Answer

Try this: DECLARE @input varchar (100) = '11111, 64,-6523125485,05/08/2018 08:29:20,53' SELECT SUBSTRING (@input, 1, Index1 - 1) [C1] , SUBSTRING (@input, Index1 + 1, Index2 - Index1 - 1) [C2] , SUBSTRING (@input, Index2 + 1, Index3 - Index2 - 1) [C3] , SUBSTRING (@input, Index3 + 1, Index4 - Index3 - 1) [C4] , SUBSTRING (@input, Index4 + 1, LEN(@input) - Index4) [C5] , CASE WHEN RIGHT ( SUBSTRING (@input, Index3 + 1, Index4 - Index3 - 1), 1) % 2 = 0 THEN 'out' else 'in' end [Mod] FROM ( SELECT CHARINDEX( ',' , @input) [Index1] , CHARINDEX( ',' , @input, CHARINDEX( ',' , @input) + 1) [Index2] , CHARINDEX( ',' , @input, CHARINDEX( ',' , @input, CHARINDEX( ',' , @input) + 1) + 1) [Index3] , CHARINDEX( ',' , @input, CHARINDEX( ',' , @input, CHARINDEX( ',' , @input, CHARINDEX( ',' , @input) + 1) + 1) + 1) [Index4] )IndexCalc

Bhavesh Vankar · Answer

this is my tables pattern 11111, 64,-6523125485,05/08/2018 08:29:20,53 when i try to fetch data from table using this query its occur error Conversion failed when converting date and/or time from character string. only single column in my table to fetch data i cant understand what to do ?

Kamil Krupski · Answer

So do you have some pattern in your column? Or data there are randomly added? Your first pattern: 1003,1253569585,05/05/2020 10:25:13 is diffrent than: 11111, 64,-6523125485,05/08/2018 08:29:20,53 If you don't have a pattern in your column, it will be hard to pull the data out.

Bhavesh Vankar · Answer

suppose i have a Table1 and data like 11111, 64,-6523125485,05/08/2018 08:29:20,53 into column 1 and column name is Filed1 then how do i separate it. using column name.

Bhavesh Vankar · Answer

its working but when im going to fetch perticular column from my table then occur error Conversion failed when converting date and/or time from character string.

Separate Data in multi column

Answers (6)