Generating a query to find records containing specified character-CodePudding

I am working on a Ruby on Rails project. From the given list, I want to retrieve all the Courses that start with a 1 (basically, Year 1 Courses).

For example, I would like

ELEC ENG 1100 Analog Electronics
ENG 1001 Introduction to Engineering
MATHS 1011 Mathematics IA

And not

COMP SCI 2201 Algorithm & Data Structure Analysis
COMP SCI 3001 Computer Networks & Applications

(even they contain 1 in them, but not in the starting position).

I have stored my Course names as one string. How could I go about querying this? I tried

Course.where("name LIKE '%1%'")

and this returns all the Courses that contain 1 in them. But this is not what I want. How could I generate a query for what I require?

CodePudding user response：

The logic of SQL behind your query is

SELECT *
FROM
(
    VALUES
    ('ELEC ENG 1100 Analog Electronics'),
    ('ENG 5001 Introduction to Engineering'),
    ('COMP SCI 2201 Algorithm & Data Structure Analysis'),
    ('MATHS 1011 Mathematics IA')
)t(topic)
WHERE SUBSTRING(topic,(PATINDEX('%[0-9]%',topic)),1) = 1

If someone could translate to ruby-on-rail

CodePudding user response：

Let's define searchable character

course_year = 1

If you need to filter courses that contain study year after white space

Course.where("name LIKE ?", "% #{course_year}%")
Course.where("name ~ ?", " #{course_year}") # works in PostgreSQL