Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-03-17 : 09:24:26
|
| If I want to search for a character which is, say, A-Z or ] how do I do that in a PATINDEX?DECLARE @strPattern nvarchar(1000)SELECT @strPattern = '%[A-Za-z]%' SELECT '123A456', PATINDEX(@strPattern , '123A456')-- Answer 4, correctSELECT @strPattern = '%[A-Za-z\]]%' -- "\" used as ESCAPESELECT '123]456', PATINDEX(@strPattern, '123]456') -- Where does ESCAPE go here?!!Kristen |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-03-17 : 09:46:55
|
DECLARE @strPattern nvarchar(1000)SELECT @strPattern = '%[A-Za-z]%' SELECT '123A456', PATINDEX(@strPattern , '123A456')-- Answer 4, correctSELECT @strPattern = '%[A-Za-z\]]%' -- "\" used as ESCAPESELECT '123]456', PATINDEX(@strPattern, '123\]456') -- Where does ESCAPE go here?!!--Answer 4 correct Kristen ?Duane.  |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-17 : 13:18:10
|
"Answer 4 correct Kristen ?"Nope, I reckon its matched the "\" followed by a "]"  But thanks for the attempt!Next please!Kristen |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-03-17 : 14:17:24
|
Good question Kristen!I had a look around (and a play), and haven't found an answer either I guess you can do something like..SELECT PATINDEX('%[A-Za-z¬]%', replace('123]456', ']', '¬'))...and I suppose you might have to, but that doesn't really answer the question!  Ryan Randallwww.monsoonmalabar.comIdeas are easy. Choosing between them is the hard part. |
|
|
|
twhelan1
Yak Posting Veteran
71 Posts |
Posted - 2006-03-17 : 15:05:45
|
Well this may be bringing a hammer to fix a broken vase, but I found an article on extending T-SQL's regular expression capabilities, which might make what you're trying easier. Though that's a simple pattern and it seems that it should be straight forward.  [url]http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx[/url]~Travis |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-19 : 04:25:19
|
| "SELECT PATINDEX('%[A-Za-z¬]%', replace('123]456', ']', '¬'))"hadn't thought of that. Its a one-off, so I can go with a Bodge, ta.(Actually I'll just REPLACE with an existing Valid character, rather than a "new one")"extending T-SQL's regular expression capabilities"Yup I've used that in the past, but this is a production server so I don't want to introduce any COM stuff that hasn't gone through the full QA - unfortunately!Kristen |
|
|
|
|
|
|
|
|
ESCAPE character in PATINDEX?