Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use PATINDEX with a variable containing '[' and ']' Expand / Collapse
Author
Message
Posted Thursday, February 20, 2014 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:29 PM
Points: 6, Visits: 86
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.
Post #1543613
Posted Thursday, February 20, 2014 10:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 3,572, Visits: 8,008
Is the bold part an error on your post or on your query?
SELECT PATINDEX('%[[]' + @filename + '][]%',@FILEPATH)



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1543624
Posted Thursday, February 20, 2014 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:29 PM
Points: 6, Visits: 86
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)

Post #1543626
Posted Thursday, February 20, 2014 10:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1543630
Posted Thursday, February 20, 2014 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:29 PM
Points: 6, Visits: 86
Brilliant that works for me.

thank you so much Luis!
Post #1543656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse