Wednesday, 26 October 2016

STUFF Function in SQL Server

Stuff function is used to insert a string into another string. Basically, it deletes characters from a source string and inserts another string at specified position.
Stuff Function in Sql

Syntax:
Stuff function syntax in sql
Here,
Character_exp is an expression of character data to be modified.
Start is an integer that specifies start position in character_exp to delete and insert another string (i.e. replacewith_exp) from here.
length is an integer, specifies the no. of characters to delete.
Replacewith_exp is character expression to be inserted at the start position.

Example 1:
Select STUFF('www.google.com',5,6,'c-sharpcorner')

In above example, we have specified the location to delete(i.e. 5) and delete 6 characters from the start position and add another string in this place.

Example 2:
In the second example, We have inserted one string between in another string.

Example 3: Masking Credit Card Number using STUFF function 
In above example, We have masked all numbers except last four of a Credit card number.

Example: 4 STUFF function returning Null Value
If start position is 0 then a null value is returned. If Start position is greater than the length of initial character expression then again null value is returned. If length(integer parameter) of string to delete is negative then a null value is returned. 

0 comments:

Post a Comment

Subscribe Now

Popular Posts

Contact us

Name

Email *

Message *

Subscribe us on YouTube

Like us on Facebook

Follow us on Google+