CODEWITHSUNDEEP
sqlreplacesql-like
GG24
GG24

Reputation: 303

SQL - How to use a LIKE Matching pattern with a Replace ?

I'm trying to make a replace with an sql request, but I also need to use a matching pattern in the replace request.

Let's suppose I have a database with one column movie and three fields : 'Batman Begins', 'The dark knight', 'the dark knight rises'.

And my request would look something like that : 

SELECT replace(movie, 'kn%t','') as movie FROM db_movie;

or something like that : 

SELECT replace(movie, movie like ('kn%t'),'') as movie FROM db_movie;

And I should get the following output :

('Batman Begins', 'the dark','the dark rises')

But I doesn't work, so do you have an idea how I could do that ?

And I need for my true request to use the '%' matching pattern, not just directly write the word "knight" that could be enough for that example.

Thank you very much for your answers :)

Upvotes: 0

Views: 3990

Answers (3)

Abhishek_Itachi
Abhishek_Itachi

Reputation: 159

Try the following query-:

SELECT CASE WHEN MOVIE LIKE '%KNIGHT%' THEN 
REPLACE(MOVIE,'KNIGHT','') ELSE MOVIE END AS MOVIE 
FROM DB_MOVIE

SQL SERVER

Upvotes: 1

IShubh
IShubh

Reputation: 364

Try the following query-:

select case when movie like '%knight%' then 
replace(movie,'knight','') else movie end as movie 
FROM db_movie

SQL Server

Upvotes: 1

ervinjohnestrera
ervinjohnestrera

Reputation: 11

In SQL SERVER, there's a function CHARINDEX() that will help you.

Select movie from db_Movie where CHARINDEX('dark',movie) <> 0

Upvotes: 1

Related Questions