How do we use STUFF in Sql Server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

SQL

--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

Posted 4-Jun-15 3:33am

StanlyJose

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)

Mohamed El khames Bakir · Answer 1 · 2015-06-04T03:44:00

From MSDN: https://msdn.microsoft.com/en-us/library/ms188043.aspx[^]

SQL

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.

CHill60 · Answer 2 · 2015-06-04T03:50:00

Solution 2

Further to solution 1 have a look at this CP article Most Commonly Used Functions in SQL Server 2005/2008[^]

Posted 4-Jun-15 3:50am

CHill60