在SQL中将字符串转换为自定义格式 [英] Convert of a string in SQL to customize format
问题描述
亲爱的朋友们，
我有一个字符串： -
'25 / 26 * 34 + 21'
我想将它转换成这个
'[25] / [26] * [34] + [21]'。
请帮忙，因为我已经尝试了很多。
我尝试过：
我尝试使用CharIndex，替换但没有成功。解决方案
说实话你会用你的演示语言做得更好：SQL字符串处理很差，它对正则表达式的支持是......嗯...可怕。
如果你使它运行起来，它将很慢并且难以维护，但是PATINDEX和STUFF可以实现，如下例所示： sql server - SQL替换函数里面的正则表达式模式？ - 堆栈溢出 [ ^ ] - 这不会完全符合您的要求，但它会给您一般的想法，并向您展示您正在做多少工作。
说真的，在您的演示代码中执行：SQL非常适合存储和关联信息，但它不是为重型字符串操作而设计的。
我完全同意@ OriginalGriff如果有人告诉你不同他们错了：笑：
但是，我注意到你的评论，你似乎别无选择，所以这里有另一种方法使用REPLACE
声明 @ Test varchar （ 125 ）= ' 25/26 * 34 + 21 + 15'
选择 ' [' + replace（替换（替换（替换（ @ Test ，' +'，' < span class =code-string>] + ['），' - ' ，' ] - ['），' *'，' ] * ['），' /'，' ] / ['）+ ' ]'注意事项：
- 我只使用+，-，*和/- 您必须扩展您想要使用的任何其他字符的原则
- 请务必阅读 REPLACE（Transact-SQL）| Microsoft Docs [ ^ ]
- 发现[和]在两端添加并考虑如果您的字符串以您要替换的任何字符开头或结尾会发生什么
- 尝试在表示层中找到一种方法
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.解决方案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.
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
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
这篇关于在SQL中将字符串转换为自定义格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
