How to use PATINDEX with a variable containing '[' and ']'

Question

Post reply

How to use PATINDEX with a variable containing '[' and ']'

JimReeves71

Default port

Points: 1410
More actions
February 20, 2014 at 10:02 am

#73562

Ok my questions is how do i search for a variable which contains '[' and ']' in another string using PATINDEX
I have the following example:
DECLARE @filename CHAR(64)
DECLARE @FILEPATH VARCHAR(MAX)
SET @filename = 'ASTERIX [Converted].eps.ai'
SET @FILEPATH = ':Volumes:Art WIP: CHILDRENS:ASTERIX: ALBUMS USING NEW FRENCH ARTWORK:20 - Folder:Links:ASTERIX [Converted].eps.ai'
When i try : SELECT PATINDEX('%[[]' + @filename + '][]%',@FILEPATH)
I get 0 returned when you see clearly that @filename exists in @FILEPATH.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Is the bold part an error on your post or on your query?

SELECT PATINDEX('%[[]' + @filename + '][]%',@FILEPATH)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

JimReeves71 Default port Points: 1410 More actions · Answer 2

the whole expression returns 0....try it as follows:

DECLARE @filename CHAR(64)

DECLARE @FILEPATH VARCHAR(MAX)

SET @filename = 'ASTERIX [Converted].eps.ai'

SET @FILEPATH = ':Volumes:Art WIP: CHILDRENS:ASTERIX: ALBUMS USING NEW FRENCH ARTWORK:20 - Asterix in Corsica:9780752866444_Asterix Corsica PB Folder:Links:ASTERIX [Converted].eps.ai'

SELECT PATINDEX('%[[]' + @filename + '][]%',@FILEPATH)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

This will return the correct results but only if you change your filename data type to varchar:

SELECT CHARINDEX(@filename,@FILEPATH), PATINDEX('%' + REPLACE( @filename, '[', '[[]') + '%',@FILEPATH)

This should show you the difference

DECLARE @filename varCHAR(64)

SET @filename = 'ASTERIX [Converted].eps.ai'

SELECT '%' + @filename + '%'

GO

DECLARE @filename CHAR(64)

SET @filename = 'ASTERIX [Converted].eps.ai'

SELECT '%' + @filename + '%'

GO

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

JimReeves71 Default port Points: 1410 More actions · Answer 4

Brilliant that works for me.

thank you so much Luis!:-)