How to replace character from middle of words ??

Question

Post reply

How to replace character from middle of words ??

gouri92

SSC Veteran

Points: 259
More actions
September 15, 2014 at 2:58 am

#296296

[font="Arial Black"]Hi,
I have 10 digit Mobile numbers in one column of table.
I have to display the data in the following way
933445758293******82
123456789012******90
I have to display two digits from starting and two digit from last
and remaining six digit should be replace with any special character.
Please help me.
[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

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

Neil Burton SSC-Insane Points: 23002 More actions · Answer 1

declare @mobile as varchar(10) = '9334457582'

select replace(@mobile,SUBSTRING(@Mobile,3,6),'******')

Does this do what you want?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Quick solution, allows for variable length input

😎

;WITH SAMPLE_DATA(MOBILE) AS

(SELECT * FROM

(VALUES

('9334457582')

,('1234567890')

) AS X(MOBILE)

)

SELECT

SD.MOBILE

,STUFF(SD.MOBILE,3,LEN(SD.MOBILE)-3,REPLICATE('*',LEN(SD.MOBILE)-3))

FROM SAMPLE_DATA SD;

Results

MOBILE

---------- -----------

9334457582 93*******2

1234567890 12*******0

Neil Burton SSC-Insane Points: 23002 More actions · Answer 3

Eirikur Eiriksson (9/15/2014)
Quick solution, allows for variable length input
😎
;WITH SAMPLE_DATA(MOBILE) AS
(SELECT * FROM
(VALUES
('9334457582')
,('1234567890')
) AS X(MOBILE)
)
SELECT
SD.MOBILE
,STUFF(SD.MOBILE,3,LEN(SD.MOBILE)-3,REPLICATE('*',LEN(SD.MOBILE)-3))
FROM SAMPLE_DATA SD;
Results
MOBILE
---------- -----------
9334457582 93*******2
1234567890 12*******0

One minor quibble with that Eirikur, that only gives one digit visible at the end :ermm:

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Neil Burton SSC-Insane Points: 23002 More actions · Answer 4

gouri92 (9/15/2014)
[font="Arial Black"]Hi,
I have 10 digit Mobile numbers in one column of table.
I have to display the data in the following way
933445758293******82
123456789012******90
I have to display two digits from starting and two digit from last
and remaining six digit should be replace with any special character.
Please help me.
[/font]

Can you post the exact code that caused the error message. I suspect I know what you've done but I can't see without the code you used.

N

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

BWFC (9/15/2014)
Eirikur Eiriksson (9/15/2014)
Quick solution, allows for variable length input
😎
;WITH SAMPLE_DATA(MOBILE) AS
(SELECT * FROM
(VALUES
('9334457582')
,('1234567890')
) AS X(MOBILE)
)
SELECT
SD.MOBILE
,STUFF(SD.MOBILE,3,LEN(SD.MOBILE)-3,REPLICATE('*',LEN(SD.MOBILE)-3))
FROM SAMPLE_DATA SD;
Results
MOBILE
---------- -----------
9334457582 93*******2
1234567890 12*******0
One minor quibble with that Eirikur, that only gives one digit visible at the end :ermm:

Opps :w00t:

Sorry about that, getting sloppy am I;-)

Here is a parametrized version

😎

DECLARE @DIGITS_LEFT INT = 2;

DECLARE @DIGITS_RIGHT INT = 2;

;WITH SAMPLE_DATA(MOBILE) AS

(SELECT * FROM

(VALUES

('9334457582')

,('1234567890')

) AS X(MOBILE)

)

SELECT

SD.MOBILE

,STUFF(SD.MOBILE,@DIGITS_LEFT + 1,LEN(SD.MOBILE)

- (@DIGITS_LEFT + @DIGITS_RIGHT),REPLICATE('*',LEN(SD.MOBILE)

- (@DIGITS_LEFT + @DIGITS_RIGHT)))

FROM SAMPLE_DATA SD;

Neil Burton SSC-Insane Points: 23002 More actions · Answer 6

create table #tblRegistration

(

Phone1 varchar (10)

)

insert into #tblregistration

select '9334457582' union

select '1234567890'

select

replace(Phone1,SUBSTRING(phone1,3,6),'******') ---This one

from #tblregistration

drop table #tblRegistration

This should stop the error message. Just include the marked line in your select list. Let me know how you get on please.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 7

Forgive my impudence, but these solutions seem rather "clever." Given the problem domain, isn't this simpler?

declare @mobile char(10), @maskChar char(1)

select @mobile = '9334457582', @maskChar = '*'

select left(@mobile, 2) + replicate(@maskChar, 6) + right(@mobile, 2)

select @mobile = '1234567890', @maskChar = '#'

select left(@mobile, 2) + replicate(@maskChar, 6) + right(@mobile, 2)

-- if the lengths need to be flexable, then this can be extended as such:

declare @leftLen int, @rightLen int, @mobileLen int

select @leftLen = 3, @rightLen = 4, @mobileLen = len(@mobile)

select left(@mobile, @leftLen) + replicate(@maskChar, @mobileLen - @leftLen - @rightLen) + right(@mobile, @rightLen)

Don Simpson

I'm not sure about Heisenberg.