eZeeNow.com

it's your's

STUFF function in SQL Server

The STUFF string function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
 
Using STUFF Function you can remove and insert the new string.

-- Insert One String Into Another String at a Specific Location

DECLARE @string1 VARCHAR(20) = 'Microsoft SQL '
DECLARE @string2 VARCHAR(20) = 'SQL Server 2012'

SELECT @string1 as OldString1, STUFF(@string1, 14, 0, 'Server ') AS 'New String 1'
SELECT @string2 as OldString2, STUFF(@string2, 15, 1, '4 CTP1') AS 'New String 2'
OUTPUT
OldString1           New String 1
-------------------- ---------------------------
Microsoft SQL Microsoft SQLServer

(1 row(s) affected)

OldString2 New String 2
-------------------- -------------------------
SQL Server 2012 SQL Server 2014 CTP1

(1 row(s) affected)
Masking Credit Card with STUFF Function.

--Mask a Credit Card Number
DECLARE @CreditCardNumber VARCHAR(20)
SET @CreditCardNumber = '4111111111111111'

SELECT STUFF(@CreditCardNumber, 1, LEN(@CreditCardNumber) - 4,
REPLICATE('X', LEN(@CreditCardNumber) - 4)) AS [Output]


OUTPUT
  Output
--------------------
XXXXXXXXXXXX1111

(1 row(s) affected)






Pingbacks and trackbacks (1)+

Loading