我们如何在Sql Server中使用STUFF [英] How do we use STUFF in Sql Server
问题描述
- 在@PhoneNumber变量中声明/获取电话号码
声明 @ PhoneNumber nvarchar ( 15 )= ' 9710600000'
- 以下选择查询可以将相关的符号和空格填入上述位置
选择 案例 何时 Len( @ PhoneNumber )> 10 然后东西(东西(东西)( @ PhoneNumber , 1 , 0 ,' +'), 4 , 0 , ' '), 9 , 0 ,' ')
< span class =code-keyword>当 Len( @ PhoneNumber )= 10 < span class =code-keyword>然后东西(东西( @ PhoneNumber , 5 , 0 ,' '),< span class =code-digit> 1 , 0 ,' + 91')
当 Len( @ PhoneNumber )< 10 然后 ' 错误的手机号码
结束 作为 PhoneNumber
- 输出将如下
+91 9710 600000
< blockquote>来自MSDN: https://msdn.microsoft.com/en-us/library/ms188043.aspx [ ^ ]
STUFF(character_expression,start,length,replaceWith_expression)
character_expression
是字符数据的表达式。 character_expression可以是字符或二进制数据的常量,变量或列。
开始
是一个整数值,指定要到的位置开始删除和插入。如果start或length为负数,则返回空字符串。如果start比第一个character_expression长,则返回空字符串。 start可以是bigint类型。
长度
是一个整数,指定要删除的字符数。如果length比第一个character_expression长,则删除发生在最后一个character_expression中的最后一个字符。 length可以是bigint类型。
replaceWith_expression
是字符数据的表达式。 character_expression可以是字符或二进制数据的常量,变量或列。这个表达式将替换从start开始的character_expression的长度字符。
进一步解决方案1看看这篇CP文章 SQL Server 2005/2008中最常用的函数 [ ^ ]
--Declare / get the Phone number in the @PhoneNumber variable
Declare @PhoneNumber nvarchar(15) = '9710600000'
--The below select query can stuff the relevant symbol and space in mentioned position
Select Case When Len(@PhoneNumber)> 10 then Stuff(Stuff(Stuff(@PhoneNumber,1,0,'+'),4,0,' '),9,0,' ')
When Len(@PhoneNumber)= 10 then Stuff(Stuff(@PhoneNumber,5,0,' '),1,0,'+91 ')
When Len(@PhoneNumber)< 10 then 'Wrong Mobile Number'
End As PhoneNumber
--The Output will be as below
+91 9710 600000
From MSDN: https://msdn.microsoft.com/en-us/library/ms188043.aspx[^]
STUFF ( character_expression , start , length , replaceWith_expression )
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
start
Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.
length
Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.
replaceWith_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.
Further to solution 1 have a look at this CP article Most Commonly Used Functions in SQL Server 2005/2008[^]
这篇关于我们如何在Sql Server中使用STUFF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!