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


(1 row(s) affected)

Comments (2) -

  • office jobs

    4/5/2019 7:34:35 AM | Reply

    I  reckon  something  truly  interesting about your  site  so I  saved to bookmarks .

  • security work

    4/10/2019 3:55:08 AM | Reply

    I dugg some of you post as I thought  they were  invaluable   handy

Pingbacks and trackbacks (1)+