How to implement string whitin a string in SQL

Question

It's one of the pain that non-English language web designers comprehend:

Imagine I have a cell that it's value is '450\k\200', as you see k is placed in between since it's an English character but if it's '450\ک\200' the non-English ک character is shown in the right which is a problem about non-English languages.

I can apply a trick and insert it inside table, tr, td tags like this:

<table><tr>
<td>450\</td><td>ک</td><td>\200<td>
</tr></table>

which works. The question is about the query, how can I split text by \ character then implement related tags as the format I just mentioned? I thought something like following can be used:

SELECT STRING_AGG( (SELECT value FROM STRING_SPLIT ( '450\k\200' , '\' )) , ',')

But I get the error saying: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How can I deal with this weird problem? I appreciate if you suggest any trick other than table tag, I even tried pre or code tags but even it didn't resolve the problem.

Your problem description is not very clear, sorry. Are you trying to solve an SQL problem with HTML? But by the looks of it, it's more likely a Unicode BIDI problem. — Mr Lister
– Mr Lister, Commented Sep 2, 2019 at 6:42
Correct syntax is SELECT STRING_AGG(value, ',') FROM STRING_SPLIT('450\k\200', '\' ). Note, that STRING_SPLIT doesn't quarantee the order of the substrings. — Zhorov
– Zhorov, Commented Sep 2, 2019 at 6:44
I tried to enlighten the matter, I found a trick to handle the problem by using table, tr and td tags when I want to show it manually, but the question is about when it's populated by the query result, how to change the query result so that the cell value be formatted as I mentioned? — Muhammad Musavi
– Muhammad Musavi, Commented Sep 2, 2019 at 6:47
@Zhorov Thanks, you are right, but how to implement table, tr and td tags then? — Muhammad Musavi
– Muhammad Musavi, Commented Sep 2, 2019 at 6:52

Muhammad Musavi · Accepted Answer · 2019-09-02 07:25:43Z

1

Thanks to @Zhorov who pointed to the solution, I completed the query and it worked, here is the query:

    SELECT '<table><tr>' + STRING_AGG('<td>' + value +'</td>', '') + '</tr></table>'
    FROM STRING_SPLIT(N'450\ک\200', '\' );

answered Sep 2, 2019 at 7:25

Muhammad Musavi

2,7462 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zhorov Over a year ago

Consider simple replacement: SELECT CONCAT('<table><tr><td>', REPLACE('450\k\200', '\', '</td><td>'), '</td></tr></table>')

Muhammad Musavi Over a year ago

@ Zhorov, +1, amazing solution, you didn't use STRING_AGG() and STRING_SPLIT() functions and you achieved the same result just by utilizing CONCAT() and REPLACE() functions, although it seems a little bit confusing plus since I don't want to remove backslash in the result, I prefer this one: SELECT CONCAT('<table><tr><td>', REPLACE('450\k\200', '\', '\</td><td>'), '</td></tr></table>')

Collectives™ on Stack Overflow

How to implement string whitin a string in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related