SQL PATINDEX find the first non-matching special character

Question

SQL PATINDEX find the first non-matching special character

Dávid Laczkó 1

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.

Viorel 126.9K Reputation points

2022-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 126.9K 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.

2 answers

Your answer

Viorel 126.9K Reputation points

2022-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 126.9K 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.

Answer 1

Erland Sommarskog 133.7K MVP Volunteer Moderator

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 133.7K 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 126.9K 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 133.7K 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 133.7K 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 CTE

Also, 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.

Answer 2

Olaf Helper 47,616

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 [^]

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?

Share via

SQL PATINDEX find the first non-matching special character

2 answers

Your answer