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, July 1, 2014 4:40 AM
Points: 6, Visits: 83
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: Today @ 11:34 AM
Points: 3,311, Visits: 7,138
Is the bold part an error on your post or on your query?
SELECT PATINDEX('%[[]' + @filename + '][]%',@FILEPATH)



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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, July 1, 2014 4:40 AM
Points: 6, Visits: 83
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: Today @ 11:34 AM
Points: 3,311, Visits: 7,138
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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, July 1, 2014 4:40 AM
Points: 6, Visits: 83
Brilliant that works for me.

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

Add to briefcase

Permissions Expand / Collapse