CODEWITHSUNDEEP
sql-servert-sqlsql-like
SweetNeetu
SweetNeetu

Reputation: 21

Using a variable and pattern matching in SQL LIKE

I have a sql table where one of the columns in 'Action' an nvarchar(max). This column can have values like HelloKitty_1.MP4 or HeMan_2.MP4, or SpiderMan_1.MP4, Hulk_4.MP4.. Here 1, 2, 1 is what I call videoIndex.

I am writing a stored procedure in SQL where I pass in the videoIndex (int) and it returns me all rows with that index. So in example above, if pass in 1, it should return me HelloKitty_1.MP4 and SpiderMan_1.MP4.

I did the following snippet but I am getting this error "Conversion failed when converting the varchar value '%_' to data type int."

DECLARE @SearchPattern varchar(40)  
SET @SearchPattern = '%' + '_' + @videoindex + '.MP4'  

DECLARE @WatchVideoCount int;  
SET @WatchVideoCount =   
     (SELECT count(*)
     FROM [dbo].[VideoWatched]
     WHERE 
     [Action] LIKE @SearchPattern)  

SELECT @WatchVideoCount as TotalTimesUsersWatchedVideo, @videoindex as videoIndex

Upvotes: 0

Views: 2110

Answers (1)

Wajih
Wajih

Reputation: 4383

Modify this:

SET @SearchPattern = '%' + '_' + @videoindex + '.MP4'

To be:

SET @SearchPattern = '%' + '_' + CAST(@videoindex AS varchar(30)) + '.MP4'

Or you can use

SET @SearchPattern = CONCAT('%' , '_' , @videoindex , '.MP4')

Upvotes: 1

Related Questions