4,707 questions
The characters in brackets [ ] have to be unique, and you can escape wildcards with a slash; this here works:
SELECT PATINDEX(N'%[/^][/^]%', N'^a^bc^^') as [^]
SQL PATINDEX find the first non-matching special character
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 52%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDV%3C/text%3E%3C/svg%3E)
Dávid Laczkó
1
Reputation point
I was writing a functionality that is the inverse of padding, e.g. from 00123 get 123, and I wanted a function that removes any given character. I use PATINDEX with [^] (Wildcard - Character(s) Not to Match), and I tested the special characters, and for my both positive and negative surprise, I could do nothing to escape these. Namely: as a positive surprise, characters _, %, and [ doesn't even need escaping, and as a negative surprise, characters ^ and ] can not be searched without escaping, and I could not escape them the way it is described at the LIKE operator, neither on a 2012 nor on a 2019 version.
Some examples without escaping:
SELECT
PATINDEX(N'%[^_]%', N'_a_bc__') as [_]
,PATINDEX(N'%[^%]%', N'%a%bc%%') as [%]
,PATINDEX(N'%[^^]%', N'^a^bc^^') as [^]
,PATINDEX(N'%[^[]%', N'[a[bc[[') as [[]
,PATINDEX(N'%[^]]%', N']a]bc]]') as []]]
SELECT
PATINDEX(N'%[^_]%', N'a_bc__') as [_]
,PATINDEX(N'%[^%]%', N'a%bc%%') as [%]
,PATINDEX(N'%[^^]%', N'a^bc^^') as [^]
,PATINDEX(N'%[^[]%', N'a[bc[[') as [[]
,PATINDEX(N'%[^]]%', N'a]bc]]') as []]]
SELECT
PATINDEX(N'%[^_]%', N'__a_bc__') as [_]
,PATINDEX(N'%[^%]%', N'%%a%bc%%') as [%]
,PATINDEX(N'%[^^]%', N'^^a^bc^^') as [^]
,PATINDEX(N'%[^[]%', N'[[a[bc[[') as [[]
,PATINDEX(N'%[^]]%', N']]a]bc]]') as []]]
Can ^ and ] be escaped in any way?
EDIT:
I opened an idea for this as a bug.
Developer technologies | Transact-SQL
{count} votes
-
Viorel 122.7K Reputation points2022-05-24T13:55:50.65+00:00 Because some of the examples work differently if you remove all of the "N" prefixes, then probably it is a defect.
-
Dávid Laczkó 1 Reputation point
2022-05-24T15:52:15.57+00:00 Good observation, thanks! So do you confirm that there are no escape characters?
-
Viorel 122.7K Reputation points
2022-05-24T16:08:46.92+00:00 I think that '%^%' should find '^', '%[^^]%' should find any character that is not '^', but the latter does not work in case of N-strings due to defect. I do not think there are escape characters like '\' or '/'.
Maybe it can be reported to https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0 too.
Sign in to comment
2 answers
Sort by: Most helpful
-
Olaf Helper 47,516 Reputation points2022-05-24T13:33:25.88+00:00 -
Dávid Laczkó 1 Reputation point
2022-05-24T13:47:48.82+00:00 It is not looking for the first non-matching character, that would be
SELECT PATINDEX(N'%[^/^]%', N'^a^bc^^') as [^]but that is not quite clear why it works until you run this and see that it is wrong:
SELECT PATINDEX(N'%[^/^]%', N'/^a^bc^^') as [^]And the same for using backslash. Where is it documented that a slash should be used or how did you get there?
Sign in to comment -
-
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator2022-05-24T21:58:46.87+00:00 My testing suggests that for the cirumflex, you get the expected result as long as you have another character in front. Olaf was half-way there, but suggested that it was escaping. It is not. So assuming that you have will have a bigger exclusion set that just
^, there should be no difficulty.The right bracket is more problematic, though.
-
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator
2022-05-24T22:23:38.003+00:00 By the way, which versions of SQL Server do you aim to support?
-
Dávid Laczkó 1 Reputation point
2022-05-25T06:34:41.533+00:00 You are right, ^ is consistent at least, and ] isn't. However I don't think that Olaf is half-way there: I went into that street also and that way one just searches for 2 characters and not 1, and that is a source of other incorrect results.
I need to support all versions back till 2012.
Practically I will have no problems with the functionality I am writing, the question was more of theoretical, as it is good to know these - and I will open a bug report for this later and will share a link here. -
Viorel 122.7K Reputation points
2022-05-25T07:59:04.453+00:00 It was suggested to introduce the ESCAPE clause for PATINDEX, which helps in case of LIKE and ']': https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdb731b2-cb20-4a2f-b6d8-eaeaf534c929.
-
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator
2022-05-25T20:57:33.847+00:00 I need to support all versions back till 2012.
Too bad. Else the TRIM function could have saved your day:
SELECT trim(' .;^]' FROM ']^ .123')Returns
123.TRIM was introduced in SQL 2017.
-
Dávid Laczkó 1 Reputation point
2022-05-26T06:26:15.253+00:00 Yes, practically. Theoretically, as it removes from both sides of the string and the specification of the functionality is to remove only from the beginning, it is No.
Thanks for showing that it takes chars as input, I did not see this function yet. -
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator
2022-05-26T09:17:59.317+00:00 Theoretically, as it removes from both sides of the string and the specification of the functionality is to remove only from the beginning,
But that can easily be handled by adding a guard character which is not in the trim set:
DECLARE @trimstring nvarchar(200) = ']^ .123^' ; WITH CTE AS ( SELECT trim(' .;^]' FROM @trimstring + 'A') AS str ) SELECT substring(str, 1, len(str) - 1) FROM CTEAlso, I found that this produces the desired result:
SELECT PATINDEX(N'%[^^]%' COLLATE Latin1_General_BIN2, N'^a^bc^^') as [^]Casting to a binary collation is also good for performance.
However, casting a binary collation does not help with the right bracket. The only way I can think of dealing with that character is to make an initial replace() to change it to something else.
Sign in to comment -