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 find PATINDEX for [ Expand / Collapse
Author
Message
Posted Tuesday, October 4, 2011 7:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:30 AM
Points: 541, Visits: 587
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
Post #1185262
Posted Tuesday, October 4, 2011 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 7,042, Visits: 12,971
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1185467
Posted Wednesday, October 5, 2011 1:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:30 AM
Points: 541, Visits: 587
Thanks Lutz. Appreciate the help. - you helped where google and BOL etc. failed!

Jason
----
Post #1185685
Posted Wednesday, October 5, 2011 1:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 7,042, Visits: 12,971
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1186149
Posted Monday, April 21, 2014 6:57 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 452, Visits: 848
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')

Post #1563657
Posted Tuesday, April 22, 2014 1:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,343, Visits: 3,809
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
Post #1563703
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse