All geek questions in one place

Get time in a column without text

in the database, I have columns. - My request is -

SELECT Emp_Name, Schedule 
FROM emp_schedule

It returns this result set:

Emp_Name        Schedule

M.S.Cave        10:00 AM - 7:00 PM Semi Flexi
M.S.Cave        10:00 AM - 7:00 PM Semi Flexi
K.V.Boreta      9:00 AM - 6:00 PM Fixed
K.V.Boreta      9:00 AM - 6:00 PM Fixed
R.M.Mendoza     Branch 8:30 AM to 5:30 PM Sunday RD
R.M.Mendoza     Branch 8:30 AM to 5:30 PM Sunday RD

But I want to display the "Schedule" column with time, not counting the text.

Emp_Name        Schedule

M.S.Cave        10:00 AM - 7:00 PM
M.S.Cave        10:00 AM - 7:00 PM
K.V.Boreta      9:00 AM - 6:00 PM
K.V.Boreta      9:00 AM - 6:00 PM
R.M.Mendoza     8:30 AM to 5:30 PM
R.M.Mendoza     8:30 AM to 5:30 PM

Is it possible to do this without TRIM, adding columns or editing a table, etc.?

+4
source share
2 answers

try it

I used the REVERSE, PATINDEX, SUBSTRINGto achieve it. Ignore CTE as I just want to use a temporary table.

;WITH CTE (Emp_Name, Schedule)
AS (
    SELECT 'M.S.Cave', '10:00 AM - 7:00 PM Semi Flexi'
    UNION ALL
    SELECT 'M.S.Cave', '10:00 AM - 7:00 PM Semi Flexi'
    UNION ALL
    SELECT 'K.V.Boreta', '9:00 AM - 6:00 PM Fixed'
    UNION ALL
    SELECT 'K.V.Boreta', '9:00 AM - 6:00 PM Fixed'
    UNION ALL
    SELECT 'R.M.Mendoza', 'Branch 8:30 AM to 5:30 PM Sunday RD'
    UNION ALL
    SELECT 'R.M.Mendoza', 'Branch 8:30 AM to 5:30 PM Sunday RD'
    )
, CTE2 AS (
    SELECT *
    , SUBSTRING(Schedule, PATINDEX('%[0-9]%', Schedule), LEN(Schedule)) SS
    FROM CTE
    )
SELECT *
,REVERSE(SUBSTRING(REVERSE(SS), PATINDEX('%[0-9]%', REVERSE(SS)) - 3, LEN(SS)))
FROM CTE2

And the result will be similar to

+-------------+-------------------------------------+-------------------------------+--------------------+
|  Emp_Name   |              Schedule               |              SS               |  (No column name)  |
+-------------+-------------------------------------+-------------------------------+--------------------+
| M.S.Cave    | 10:00 AM - 7:00 PM Semi Flexi       | 10:00 AM - 7:00 PM Semi Flexi | 10:00 AM - 7:00 PM |
| M.S.Cave    | 10:00 AM - 7:00 PM Semi Flexi       | 10:00 AM - 7:00 PM Semi Flexi | 10:00 AM - 7:00 PM |
| K.V.Boreta  | 9:00 AM - 6:00 PM Fixed             | 9:00 AM - 6:00 PM Fixed       | 9:00 AM - 6:00 PM  |
| K.V.Boreta  | 9:00 AM - 6:00 PM Fixed             | 9:00 AM - 6:00 PM Fixed       | 9:00 AM - 6:00 PM  |
| R.M.Mendoza | Branch 8:30 AM to 5:30 PM Sunday RD | 8:30 AM to 5:30 PM Sunday RD  | 8:30 AM to 5:30 PM |
| R.M.Mendoza | Branch 8:30 AM to 5:30 PM Sunday RD | 8:30 AM to 5:30 PM Sunday RD  | 8:30 AM to 5:30 PM |
+-------------+-------------------------------------+-------------------------------+--------------------+
+2
source

I tried to do the following:

select Emp_name,
        reverse(SUBSTRING(reverse(Schedule),charindex(' MP ',REVERSE(Schedule)),len(Schedule)))
        ,substring(Schedule,charindex(' AM ',Schedule)-5,charindex(' PM ',Schedule)-charindex(' AM ',Schedule)+8)
        from emp_schedule
+1
source

Source: https://habr.com/ru/post/1671862/

More articles:


All Articles