how to remove year from column in sql?

Question

I have this solution in python that I am trying to replicate in SQL Server.

movies_df['title']

title
Toy Story (1995)
The 2000 Year Old Man (1975)
The With (2) Sant (1999)

I run this code and it gets rid of the year

movies_df['title'] = movies_df.title.str.replace('((\d\d\d\d))', '')

Output:

title
Toy Story
The 2000 Year Old Man
The With (2) Sant

I have a table called movies in SQL. I want to do get the same output in SQL

select movie_title from movies

How do I solve this?

Answer 1

As you may have found out, SQL Server has no pattern replacement functionality. You'll need to use pattern matching, and then remove that many characters. I assume the value is always at the end, and always preceded by a space:

SELECT V.Title,
       STUFF(V.title,PATINDEX('% ([0-9][0-9][0-9][0-9])',V.Title),7,'')
FROM (VALUES('Toy Story (1995)'),
            ('The 2000 Year Old Man (1975)'),
            ('The With (2) Sant (1999)'))V(title)