SQL query to find multiple value from single field with input parameters

Question

Is it possible to find multiple values from a single field, that are within a set of input parameters?

The DB example would be something like this

User             Login_DT_TM
0001             1/1/2015 12:00, 1/1/2015 12:30,1/6/2015 09:00
0002             1/3/2015 13:00, 1/5/2015 16:00

And i would need something to give me this output

0001           1/1/2015 12:00
0001           1/1/2015 12:30
0002           1/3/2015 13:00

Where login_DT_TM => 1/1/2015 '00:00' AND login_DT_TM =< 1/4/2015 '23:59'

Please tag your question appropriately. SQL Server or MySQL? — Gordon Linoff, Oct 02 '15 at 19:26
The best way of doing this would be moving separate values into a separate table. — Sergey Kalinichenko, Oct 02 '15 at 19:31
Yes, it's possible to do something like this (see Juan Carlos Oropeza's comment above), but the NEED to do something like this is a sign of bad database design. :-) — pmbAustin, Oct 02 '15 at 20:06

score 0 · Answer 1 · answered Oct 02 '15 at 20:18

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Test_Table ([User] VARCHAR(10) , Login_DT_TM VARCHAR(4000))
INSERT INTO Test_Table VALUES
('0001','1/1/2015 12:00, 1/1/2015 12:30,1/6/2015 09:00'),
('0002','1/3/2015 13:00, 1/5/2015 16:00')

Query 1:

WITH X AS
(
 SELECT [User] 
       ,CAST(Split.a.value('.', 'VARCHAR(100)')  AS DATETIME) Login_DT_TM
 FROM   
     (SELECT    [User]
              ,Cast ('<X>' + Replace(Login_DT_TM, ',', '</X><X>') + '</X>' AS XML) AS Data
       FROM    Test_Table
     ) AS t 
     CROSS APPLY Data.nodes ('/X') AS Split(a) 
)
SELECT  [User]
       ,Login_DT_TM
FROM X
WHERE Login_DT_TM > '20141231'
 AND  Login_DT_TM < '20150105'

Results:

| User |        Login_DT_TM        |
|------|---------------------------|
| 0001 | 2015-01-01 12:00:00.000   |
| 0001 | 2015-01-01 12:30:00.000   |
| 0002 | 2015-01-03 13:00:00.000   |

score 0 · Answer 2 · answered Oct 02 '15 at 20:37

This should meet your expectations:

DECLARE @table TABLE
    (
      [USER] NVARCHAR(30) ,
      LOGIN NVARCHAR(300)
    );

INSERT  INTO @table
        ( [USER], LOGIN )
VALUES  ( N'0001', 
          N'1/1/2015 12:00, 1/1/2015 12:30, 1/6/2015 09:00'
          ),
        ( N'0002',
          N'1/3/2015 13:00, 1/5/2015 16:00'
          );


WITH    tmp ( userid, part , rest  )
          AS ( SELECT   [USER] ,
                        CONVERT(NVARCHAR(300),LEFT(LOGIN, CHARINDEX(',', LOGIN + ',') - 1)) ,
                        CONVERT(NVARCHAR(300),STUFF(LOGIN, 1, CHARINDEX(',', LOGIN + ',')+1, ''))
               FROM     @table
               UNION ALL
               SELECT   userid ,
                        CONVERT(NVARCHAR(300),LEFT(rest, CHARINDEX(',', rest + ',') - 1)) ,
                        CONVERT(NVARCHAR(300),STUFF(rest, 1, CHARINDEX(',', rest + ',')+1, '') )
               FROM     tmp
               WHERE    rest > ''
             )
    SELECT  tmp.userid, tmp.part
    FROM    tmp;

SQL query to find multiple value from single field with input parameters

2 Answers2