How can I convert %5B and %5D to [ and ] in a string

Question

How can I convert %5B and %5D to [ and ] in a string

guerinto

SSC-Addicted

Points: 447
More actions
August 23, 2019 at 5:57 pm

#3674052

Hi,
I have a string nvarchar(255) and I am try to convert the ASCII codes in it and also extract part of the string.
Here is an example of a value: urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1
This is what I would like to convert it to: urn:ADL:CC_LB_SF_EN_D_C_[LB_LS_TCD]_[LB_LS_CotCF]_28_1
Then I would like to select what is between urn:ADL: and the last _
I tried SELECT SUBSTRING(interaction_id, CHARINDEX('ADL:', interaction_id), CHARINDEX('_%5',interaction_id) - CHARINDEX('ADL:', interaction_id) + Len('_%5')) AS substring
to get me started with the part of the string I need, but that SELECT statement gets me: ADL:CC_CLB_SF_EN_D_C_%5
Clearly I am not very good at this string manipulation stuff, but I would like to learn.
What SELECT statement gets me to CC_LB_SF_EN_D_C_[LB_LS_TCD]_[LB_LS_CotCF]_28
Help!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

;WITH CTE AS
(
    SELECT 'urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1' Original
),
CTE1 AS
(
    SELECT *,
           REPLACE(REPLACE(CTE.Original,'%5B','['),'%5D',']') SquareBrackets
     FROM CTE
),
CTE2 AS
(
    SELECT *,
           SUBSTRING(CTE1.SquareBrackets,CHARINDEX('ADL:',CTE1.SquareBrackets,1)+LEN('ADL:'),10000) RemovedADL
    FROM CTE1
),
CTE3 AS
(
    SELECT *,
           SUBSTRING(CTE2.RemovedADL,1,LEN(CTE2.RemovedADL)-CHARINDEX('_',REVERSE(CTE2.RemovedADL))) Final  
      FROM CTE2
)
SELECT *
  FROM CTE3

You can combine it all into one expression but it wouldn't be very readable

guerinto SSC-Addicted Points: 447 More actions · Answer 2

That returns urn:ADL:CC_LB_SF_EN_D_C_[LB_LS_TCD]_[LB_LS_CotCF]_28_1 and I need ADL:CC_LB_SF_EN_D_C_[LB_LS_TCD]_[LB_LS_CotCF]_28 returned. Everything between the urn: and the last _

Also, I should have been clearer. I have hundreds, if not a thousand of these to convert, so I need a select statement that replaces every occurance of %5B with [ and %5D with ]

For each string, the logic will always be everything between the urn: and the last _

Thanks and sorry for not being clearer.

guerinto SSC-Addicted Points: 447 More actions · Answer 3

Please disregard my post #3674055. It was a reply to a post no longer there 🙁

guerinto SSC-Addicted Points: 447 More actions · Answer 4

I was hoping to have it as 1 select statement as I have to join it to other data. I do appreciate what you showed me however, as it breaks it down step by step. Now I need to figure out how to combine a REPLACE and 2 SUBSTRINGS onto 1 line!

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

guerinto wrote:

I was hoping to have it as 1 select statement as I have to join it to other data. I do appreciate what you showed me however, as it breaks it down step by step. Now I need to figure out how to combine a REPLACE and 2 SUBSTRINGS onto 1 line!

I think with something that complicated you'd be better off putting it in several CROSS APPLY operators. You can then join on results of the final CROSS APPLY

;WITH CTE AS (SELECT 'urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1' Original)
SELECT W.Original, Z.Final
  FROM CTE W
 CROSS APPLY(VALUES (REPLACE(REPLACE(W.Original,'%5B','['),'%5D',']'))) X(SquareBrackets)
 CROSS APPLY(VALUES (SUBSTRING(X.SquareBrackets,CHARINDEX('ADL:',X.SquareBrackets,1)+LEN('ADL:'),10000))) Y(RemovedADL)
 CROSS APPLY(VALUES (SUBSTRING(Y.RemovedADL,1,LEN(Y.RemovedADL)-CHARINDEX('_',REVERSE(Y.RemovedADL))))) Z(Final)

Lowell SSC Guru Points: 323518 More actions · Answer 6

i think this will fix just two items though; won't he have continued issues when he discovers %20 or other url encoded values?

i would think we need to consider all the other outlier values that will get encoded as well.

this has some scalar examples that probably point to teh right direction:

https://stackoverflow.com/questions/3833229/sql-server-url-decoding

and here is a reference to the url encoded characters in general, just to visualize.

https://www.degraeve.com/reference/urlencoding.php

This reply was modified 5 years, 3 months ago by Lowell.
This reply was modified 5 years, 3 months ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

guerinto SSC-Addicted Points: 447 More actions · Answer 7

Thanks to Jonathan AC Roberts and Lowell. I'm going to try and see if I can get this on one line or join the CTE to my existing data. For Lowell, the format of the data is very specific and while I am sure other URL encoded values may sneak into the database, I have verified %5B and %5D are the only ones I have to deal with out of ~10,000 rows.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

Just paste in your original query and I'll show you how it can be done.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

if you want it in one line then here it is, but I wouldn't recommend it

;WITH CTE AS (SELECT 'urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1' Original)
SELECT W.Original, Z.Final
  FROM CTE W
 CROSS APPLY(VALUES (SUBSTRING(SUBSTRING(REPLACE(REPLACE(W.Original,'%5B','['),'%5D',']'),CHARINDEX('ADL:',REPLACE(REPLACE(W.Original,'%5B','['),'%5D',']'),1)+4,10000),1,LEN(SUBSTRING(REPLACE(REPLACE(W.Original,'%5B','['),'%5D',']'),CHARINDEX('ADL:',REPLACE(REPLACE(W.Original,'%5B','['),'%5D',']'),1)+4,10000))-CHARINDEX('_',REVERSE(SUBSTRING(REPLACE(REPLACE(W.Original,'%5B','['),'%5D',']'),CHARINDEX('ADL:',REPLACE(REPLACE(W.Original,'%5B','['),'%5D',']'),1)+4,10000)))))) Z(Final)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Just my take on this... single and fairly short formula that does the replacements only once. This, of course, assumes that you'll always be working values where you want to throw away the first 8 characters, which is 'urn:ADL:' in your example.

DECLARE @SomeString NVARCHAR(255) = N'urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1';
;
 SELECT REPLACE(REPLACE(SUBSTRING(@SomeString,9,LEN(@SomeString)-CHARINDEX(N'_',REVERSE(@SomeString))-8),N'%5B',N'['),N'%5D',N']')
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

guerinto SSC-Addicted Points: 447 More actions · Answer 11

Thanks Jeff. This appears to be exactly what I am looking for. How would I replace the string with a field name called int_ID now? I don't know what to do with the N'. I'm not a programmer or dba. I am a business analyst and I use SQL to help me analyze data.

I tried this, but it returns empty cells. I know I need to do something with the N', but I don't know what.

SELECT REPLACE(REPLACE(SUBSTRING(int_id,9,LEN(int_id)-CHARINDEX(N'_',REVERSE(int_id))-8),N'%5B',N'['),N'%5D',N']') FROM AI_int

Sorry for the delay in responding. I was away for the weekend.

guerinto SSC-Addicted Points: 447 More actions · Answer 12

Actually, the query was fine. It was the data I joined it to was the problem. After correcting my error and using the specific data set I wanted to see, it worked perfectly.

Thanks to Jeff for teaching me something new and also to Jonathan. Your queries worked as well and taught me something as well. Hopefully I can reuse these once I study them some more.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

guerinto wrote:

Thanks Jeff. This appears to be exactly what I am looking for. How would I replace the string with a field name called int_ID now? I don't know what to do with the N'. I'm not a programmer or dba. I am a business analyst and I use SQL to help me analyze data.
I tried this, but it returns empty cells. I know I need to do something with the N', but I don't know what.
SELECT REPLACE(REPLACE(SUBSTRING(int_id,9,LEN(int_id)-CHARINDEX(N'_',REVERSE(int_id))-8),N'%5B',N'['),N'%5D',N']') FROM AI_int
Sorry for the delay in responding. I was away for the weekend.

I assume that INT_ID is an integer. That's a totally different requirement and won't contain a string to be parsed.

Edit... I forgot to refresh the screen and missed your previous post above. Glad you figured it out and thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

guerinto SSC-Addicted Points: 447 More actions · Answer 14

Thanks Jeff. It is not an integer field - int was short for interactions.