Convert of a string in SQL to customize format

Question

1.50/5 (2 votes)

See more:

Dear Friends,

I have a string :-

'25/26*34+21'

and I want it to be converted into this

'[25]/[26]*[34]+[21]'

in SQL.

Kindly help as i am have tried a lot.

What I have tried:

I tried using CharIndex, Replace but no success.

Posted 20-Nov-18 22:10pm

Varun Sareen

Updated 21-Nov-18 0:20am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-11-20T22:17:00

To be honest, you would be much better off doing this in your presentation language: SQL string handling is poor, and it's support for regular expressions is ... um ... dreadful.

If you get it working, it'll be slow and difficult to maintain, but it is possible with PATINDEX and STUFF, as this example shows: sql server - Regex pattern inside SQL Replace function? - Stack Overflow[^] - that won't do exactly what you are after, but it shoudl give you teh general idea and show you just how much work you are getting yourself into.

Seriously, do it in your presentation code: SQL is excellent at storing and relating information, but it's not designed for heavy duty string manipulation.

CHill60 · Answer 2 · 2018-11-21T00:20:00

I wholeheartedly agree with @OriginalGriff and if anyone tells you different they're wrong :laugh:

However, I note your comment that you appear to have no choice so here is an alternative approach using REPLACE

SQL

declare @Test varchar(125) = '25/26*34+21+15'

select '[' + replace(replace(replace(replace(@Test, '+',']+['), '-',']-['),'*',']*['),'/',']/[') + ']'

Points to note:
- I have only used + , - , * and / - you will have to extend the principle for any other characters you want to use
- Be sure to read the documentation at REPLACE (Transact-SQL) | Microsoft Docs[^]
- Spot the [ and ] added at either end and consider what will happen if your string begins or ends with any of the characters you are replacing
- Try to find a way to do this in your presentation layer

Convert of a string in SQL to customize format

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0