Separate Data in multi column

Bhavesh Vankar

6y857

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...

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kamil Krupski6y ago
Accepted 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 Krupski6y ago
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 Vankar6y ago
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 Krupski6y ago
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 Vankar6y ago
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 Vankar6y ago
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.