0

I'm relatively new to Oracle SQL and I am wondering if anyone is able to help me out.

I am attempting to return the first 50 rows from the History column of my Employee table. I am trying to return the instances where 'desk' is located in history after the first use of 'on'.

Thanks in advance.

SELECT SUBSTR(History, INSTR(History, ' on ') + 3) AS subject
FROM Employee
WHERE subject LIKE '%desk%'
AND ROWNUM <= 50;
Improve this question
2
  • Add some sample records and expected result to avoid confusion. cover all scenarios that you can think of and what you expect to see as result in all such cases. Commented Oct 15, 2018 at 10:41
  • Rows from a column? One column for the history? That sounds like a very bad database design. I'd rather expect a table employee_history. Commented Oct 15, 2018 at 10:46

2 Answers 2

Reset to default
3

For such more complicated like conditions, you can use REGEXP_LIKE which uses regular expressions:

SELECT SUBSTR(History, INSTR(History, ' on ') + 3) AS subject
  FROM Employee
 WHERE subject LIKE '%desk%'
   AND REGEXP_LIKE(History, '.*\son\s.*desk.*')
   AND ROWNUM <= 50

The final regular expression you have to use might be different to this one, but it should give you a start. I am pretty sure that your request is solvable in this way...

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Simply use LIKE:

SELECT SUBSTR(History, INSTR(History, ' on ') + 3) AS subject
FROM Employee
WHERE subject LIKE '%on%desk%' AND ROWNUM <= 50;

I think LIKE is preferred to regular expressions just because it should be a bit faster.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.