mssql patindex for ] symbol












1















How to specify ] symbol inside character class (MS SQL SERVER PATINDEX function)?



'%["%' - for starting bracket - it works
'%["]]%' - for ending - it does not









share|improve this question




















  • 4





    Possible duplicate of SQL Server LIKE containing bracket characters

    – snipsnipsnip
    Sep 4 '18 at 7:07











  • PATINDEX can not be used with ESCAPE keyword as it can with LIKE.

    – user10313400
    Sep 4 '18 at 7:57











  • You're right. Does this DBA.SE Question about closing square bracket help? You might find some workaround there.

    – snipsnipsnip
    Sep 5 '18 at 1:17


















1















How to specify ] symbol inside character class (MS SQL SERVER PATINDEX function)?



'%["%' - for starting bracket - it works
'%["]]%' - for ending - it does not









share|improve this question




















  • 4





    Possible duplicate of SQL Server LIKE containing bracket characters

    – snipsnipsnip
    Sep 4 '18 at 7:07











  • PATINDEX can not be used with ESCAPE keyword as it can with LIKE.

    – user10313400
    Sep 4 '18 at 7:57











  • You're right. Does this DBA.SE Question about closing square bracket help? You might find some workaround there.

    – snipsnipsnip
    Sep 5 '18 at 1:17
















1












1








1








How to specify ] symbol inside character class (MS SQL SERVER PATINDEX function)?



'%["%' - for starting bracket - it works
'%["]]%' - for ending - it does not









share|improve this question
















How to specify ] symbol inside character class (MS SQL SERVER PATINDEX function)?



'%["%' - for starting bracket - it works
'%["]]%' - for ending - it does not






sql-server character symbols patindex






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Sep 4 '18 at 8:47







user10313400

















asked Sep 4 '18 at 6:58









user10313400user10313400

83




83








  • 4





    Possible duplicate of SQL Server LIKE containing bracket characters

    – snipsnipsnip
    Sep 4 '18 at 7:07











  • PATINDEX can not be used with ESCAPE keyword as it can with LIKE.

    – user10313400
    Sep 4 '18 at 7:57











  • You're right. Does this DBA.SE Question about closing square bracket help? You might find some workaround there.

    – snipsnipsnip
    Sep 5 '18 at 1:17
















  • 4





    Possible duplicate of SQL Server LIKE containing bracket characters

    – snipsnipsnip
    Sep 4 '18 at 7:07











  • PATINDEX can not be used with ESCAPE keyword as it can with LIKE.

    – user10313400
    Sep 4 '18 at 7:57











  • You're right. Does this DBA.SE Question about closing square bracket help? You might find some workaround there.

    – snipsnipsnip
    Sep 5 '18 at 1:17










4




4





Possible duplicate of SQL Server LIKE containing bracket characters

– snipsnipsnip
Sep 4 '18 at 7:07





Possible duplicate of SQL Server LIKE containing bracket characters

– snipsnipsnip
Sep 4 '18 at 7:07













PATINDEX can not be used with ESCAPE keyword as it can with LIKE.

– user10313400
Sep 4 '18 at 7:57





PATINDEX can not be used with ESCAPE keyword as it can with LIKE.

– user10313400
Sep 4 '18 at 7:57













You're right. Does this DBA.SE Question about closing square bracket help? You might find some workaround there.

– snipsnipsnip
Sep 5 '18 at 1:17







You're right. Does this DBA.SE Question about closing square bracket help? You might find some workaround there.

– snipsnipsnip
Sep 5 '18 at 1:17














1 Answer
1






active

oldest

votes


















0














Looks like there is no way to properly escape closing bracket (]) in PATINDEX. ] alone can be written verbatim, but cannot be included in the character set.



However, according to this DBA.SE question, there are some workaround (see the linked article for the full example):




  1. Specify character range that contains ]. (note that this will match unwanted characters)


PATINDEX('%[[-^{}:,]%' COLLATE Latin1_General_BIN2, MyJSONString)



  1. Apply REPLACE before match.


PATINDEX('%[[' + CHAR(174) + '@]%', REPLACE(@test,']',CHAR(174)))



  1. Use PATINDEX twice: one for ], and the other for the rest of characters.


(NULLIF(PATINDEX('%[[{}:,]%', d.ResponseJSON), 0), NULLIF(PATINDEX('%]%', d.ResponseJSON), 0)))





share|improve this answer


























  • No. 1 seems be the one i am looking for. No. 2 cannot be as a workaround. In JSON could be symbol ] as text, not as array closing bracket. No. 3 searches two times.

    – user10313400
    Sep 5 '18 at 6:45













Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52160490%2fmssql-patindex-for-symbol%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














Looks like there is no way to properly escape closing bracket (]) in PATINDEX. ] alone can be written verbatim, but cannot be included in the character set.



However, according to this DBA.SE question, there are some workaround (see the linked article for the full example):




  1. Specify character range that contains ]. (note that this will match unwanted characters)


PATINDEX('%[[-^{}:,]%' COLLATE Latin1_General_BIN2, MyJSONString)



  1. Apply REPLACE before match.


PATINDEX('%[[' + CHAR(174) + '@]%', REPLACE(@test,']',CHAR(174)))



  1. Use PATINDEX twice: one for ], and the other for the rest of characters.


(NULLIF(PATINDEX('%[[{}:,]%', d.ResponseJSON), 0), NULLIF(PATINDEX('%]%', d.ResponseJSON), 0)))





share|improve this answer


























  • No. 1 seems be the one i am looking for. No. 2 cannot be as a workaround. In JSON could be symbol ] as text, not as array closing bracket. No. 3 searches two times.

    – user10313400
    Sep 5 '18 at 6:45


















0














Looks like there is no way to properly escape closing bracket (]) in PATINDEX. ] alone can be written verbatim, but cannot be included in the character set.



However, according to this DBA.SE question, there are some workaround (see the linked article for the full example):




  1. Specify character range that contains ]. (note that this will match unwanted characters)


PATINDEX('%[[-^{}:,]%' COLLATE Latin1_General_BIN2, MyJSONString)



  1. Apply REPLACE before match.


PATINDEX('%[[' + CHAR(174) + '@]%', REPLACE(@test,']',CHAR(174)))



  1. Use PATINDEX twice: one for ], and the other for the rest of characters.


(NULLIF(PATINDEX('%[[{}:,]%', d.ResponseJSON), 0), NULLIF(PATINDEX('%]%', d.ResponseJSON), 0)))





share|improve this answer


























  • No. 1 seems be the one i am looking for. No. 2 cannot be as a workaround. In JSON could be symbol ] as text, not as array closing bracket. No. 3 searches two times.

    – user10313400
    Sep 5 '18 at 6:45
















0












0








0







Looks like there is no way to properly escape closing bracket (]) in PATINDEX. ] alone can be written verbatim, but cannot be included in the character set.



However, according to this DBA.SE question, there are some workaround (see the linked article for the full example):




  1. Specify character range that contains ]. (note that this will match unwanted characters)


PATINDEX('%[[-^{}:,]%' COLLATE Latin1_General_BIN2, MyJSONString)



  1. Apply REPLACE before match.


PATINDEX('%[[' + CHAR(174) + '@]%', REPLACE(@test,']',CHAR(174)))



  1. Use PATINDEX twice: one for ], and the other for the rest of characters.


(NULLIF(PATINDEX('%[[{}:,]%', d.ResponseJSON), 0), NULLIF(PATINDEX('%]%', d.ResponseJSON), 0)))





share|improve this answer















Looks like there is no way to properly escape closing bracket (]) in PATINDEX. ] alone can be written verbatim, but cannot be included in the character set.



However, according to this DBA.SE question, there are some workaround (see the linked article for the full example):




  1. Specify character range that contains ]. (note that this will match unwanted characters)


PATINDEX('%[[-^{}:,]%' COLLATE Latin1_General_BIN2, MyJSONString)



  1. Apply REPLACE before match.


PATINDEX('%[[' + CHAR(174) + '@]%', REPLACE(@test,']',CHAR(174)))



  1. Use PATINDEX twice: one for ], and the other for the rest of characters.


(NULLIF(PATINDEX('%[[{}:,]%', d.ResponseJSON), 0), NULLIF(PATINDEX('%]%', d.ResponseJSON), 0)))






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 21 '18 at 6:53

























answered Sep 5 '18 at 1:38









snipsnipsnipsnipsnipsnip

8971822




8971822













  • No. 1 seems be the one i am looking for. No. 2 cannot be as a workaround. In JSON could be symbol ] as text, not as array closing bracket. No. 3 searches two times.

    – user10313400
    Sep 5 '18 at 6:45





















  • No. 1 seems be the one i am looking for. No. 2 cannot be as a workaround. In JSON could be symbol ] as text, not as array closing bracket. No. 3 searches two times.

    – user10313400
    Sep 5 '18 at 6:45



















No. 1 seems be the one i am looking for. No. 2 cannot be as a workaround. In JSON could be symbol ] as text, not as array closing bracket. No. 3 searches two times.

– user10313400
Sep 5 '18 at 6:45







No. 1 seems be the one i am looking for. No. 2 cannot be as a workaround. In JSON could be symbol ] as text, not as array closing bracket. No. 3 searches two times.

– user10313400
Sep 5 '18 at 6:45






















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52160490%2fmssql-patindex-for-symbol%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Guess what letter conforming each word

Port of Spain

Run scheduled task as local user group (not BUILTIN)