Using a variable and pattern matching in SQL LIKE

Question

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

Wajih · Accepted Answer · 2015-11-14 19:55:44Z

1

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')

edited Nov 14, 2015 at 19:55

answered Nov 14, 2015 at 19:40

Wajih

4,4152 gold badges28 silver badges42 bronze badges

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

1 Comment

Wajih Over a year ago

Or you can use SET @SearchPattern = CONCAT('%' , '_' , @videoindex , '.MP4')

Collectives™ on Stack Overflow

Using a variable and pattern matching in SQL LIKE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related