How to find PATINDEX for [

Question

Post reply

How to find PATINDEX for [

Ulysses_Infinity

SSCrazy

Points: 2588
More actions
October 4, 2011 at 7:55 am

#249244

Hi all,
How can I find the character position of the first instance of the character [ in a text string. The following doesn't work:
PATINDEX('%[%',mytextfield)
although it does work for the ] character.
Any help greatly appreciated. (I appreciated this is probably to do with the [] wildcard functionality)
rgds - Jason

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Since [ is a meta-character in wildcard expressions, you'll need to wrap it with another set of brackets:

SELECT PATINDEX('%[[]%',mytextfield)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ulysses_Infinity SSCrazy Points: 2588 More actions · Answer 2

Thanks Lutz. Appreciate the help. - you helped where google and BOL etc. failed!

Jason

----

LutzM SSC Guru Points: 107049 More actions · Answer 3

As usual, google will return some helpful info as long as you know the "magic spell".

in this case, "patindex escape" would have provided a number of helpful links. 😉

I agree regarding BOL though. The description provided for PATINDEX is kinda weak regarding the handling of meta-characters.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

davoscollective SSCertifiable Points: 6355 More actions · Answer 4

LutzM (10/4/2011)
Since [ is a meta-character in wildcard expressions, you'll need to wrap it with another set of brackets:
SELECT PATINDEX('%[[]%',mytextfield)

Interestingly, that same pattern doesn't work for the closing square bracket (I'm on 2012).

Works:

DECLARE @strPattern nvarchar(10)

SELECT @strPattern = '%[[]%'

SELECT '123]456', PATINDEX(@strPattern, '123[456')

Doesn't work:

DECLARE @strPattern nvarchar(10)

SELECT @strPattern = '%[]]%'

SELECT '123]456', PATINDEX(@strPattern, '123]456')

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 5

You are better off using the CHARINDEX function, and CHAR or NCHAR functions when dealing with special characters.

😎

DECLARE @TEST_STR NVARCHAR(128) = N'ASDFGHJ[QWER';

SELECT CHARINDEX(NCHAR(91),@TEST_STR) AS POS

Result

POS

----

8