将字符插入SQL字符串 [英] Insert character into SQL string
本文介绍了将字符插入SQL字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个nvarchar列,我需要在字符串内的固定点插入连字符.连字符必须位于最右边的字符和下一个字符之间,并再次位于右边的第3个位置,例如: 列值为
0000050704
我需要成为
0000050-70-4
或值为
0555256321
它应该是
0555256-32-1
看不到如何完成.谁能给我一点帮助?
解决方案
假设字符串可以是可变长度,则需要在表达式中使用REVERSE()或很多讨厌的LEN()值.
declare @txt varchar(100) = '0000050704'
--If using SQL Server, the STUFF() function is your friend
select REVERSE(STUFF(STUFF(REVERSE(@txt), 2, 0, '-'), 5, 0, '-'))
--if not you'll need to concatenate SUBSTRING()s
select REVERSE(SUBSTRING(REVERSE(@txt), 1, 1) + '-' + SUBSTRING(REVERSE(@txt),2, 2) + '-' + SUBSTRING(REVERSE(@txt),4, LEN(@txt)))
I have an nvarchar column I need to insert a hyphen at fixed points within the string. The hyphen need to go between the rightmost character and the next, and again in the 3rd position from the right, such as: column value is
0000050704
and I need it to be
0000050-70-4
or value is
0555256321
and it should be
0555256-32-1
Can't see how this is done. Can anyone give me a little help?
解决方案
Assuming the strings can be a variable length, you'll need to use REVERSE() or lots of nasty looking LEN() values in your expression.
declare @txt varchar(100) = '0000050704'
--If using SQL Server, the STUFF() function is your friend
select REVERSE(STUFF(STUFF(REVERSE(@txt), 2, 0, '-'), 5, 0, '-'))
--if not you'll need to concatenate SUBSTRING()s
select REVERSE(SUBSTRING(REVERSE(@txt), 1, 1) + '-' + SUBSTRING(REVERSE(@txt),2, 2) + '-' + SUBSTRING(REVERSE(@txt),4, LEN(@txt)))
这篇关于将字符插入SQL字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文