change first 12 characters from numbers to ****

Question

Post reply

change first 12 characters from numbers to ****

rodan-892007

Right there with Babe

Points: 731
More actions
March 13, 2009 at 11:33 am

#129705

I need to change the values in a field, leaving the last 4 characters only. Would like to change the characters to the left of these last 4 to ****. Please advise.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 1

Hi Robert

DECLARE @phone VARCHAR(100)

SET @phone = '1234567890123456'

SELECT REPLICATE('*', 12) + RIGHT(@phone, LEN(@phone) - 12)

Greets

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Lowell SSC Guru Points: 323482 More actions · Answer 2

same thing, different way:

[font="Courier New"]UPDATE mytable

SET myfield= '************' + RIGHT(myfield,4)

WHERE LEN(myfield) = 16 --only change items that are exactly 16 chars

SELECT * FROM mytable WHERE LEN(myfield) <> 16 [[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hkphooey SSC Veteran Points: 239 More actions · Answer 3

If you want to keep the field to be the same length as it was, but leave a maximum of 4 chars kept at the end, use

UPDATE tablename

SET fieldname=replicate(‘*’,len(fieldname)-4) + right(fieldname,4)

WHERE LEN(fieldname)>4

nwright-673099 SSC Veteran Points: 273 More actions · Answer 4

If you were trying to mask data such as a phone number or a ssn and you still want the data to have the masked appearance of the original value you could use:

DECLARE @ssn VARCHAR(100)

SET @ssn = '123-45-6789'

SELECT rtrim(ltrim(REPLICATE('*', 3) + SUBSTRING(@ssn, 4,1) + REPLICATE('*', 2) + SUBSTRING(@ssn, 7,1)+ RIGHT(@ssn, LEN(@ssn) - 7))) as SSN

Narrio......:)

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 5

And yet another 😛

declare @number varchar(100)

set @number = '12345678902331234'

select @number as [before], len(@number) as [length]

set @number = STUFF(@number,1,len(@number)-4,REPLICATE('*',len(@number)-4))

select @number as [after], len(@number) as [length]

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills