update the content in the column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update the content in the column

Author

Topic

venkat.v
Starting Member

4 Posts

Posted - 2009-08-14 : 03:05:21

hi all i am having a table which contains columns like name,DOB ...etc and has a column like contentmail.. in that column it consists of whatever content is present in mail. its jus copied and inserted in the table i.e in the contentmail column. it ll be differen for each user..in that mail it may contain numbers also like ph.no or ssn etc... now i want to update the whole column.
1) by replcing the numbers with * or x numbers should not be visible..
but the other text should be same but the number should be changed to *. plz help its really urgent

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 05:19:07

you can use replace() function to do it

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:28:47

you can create a function like this


CREATE FUNCTION MaskNumbers
(
@String varchar(max)
)
RETURNS varchar(max)
AS
begin
Declare @result varchar(max)
select
    @result=coalesce(@result,'')+case when charvalue like '[0-9]' then '*' else charvalue end from 
    (
         select substring(@String,number,1) as charvalue from 
        (
            select number from master..spt_values where type='p' and number between 1 and len(@s)
        ) as t
    ) as t1 
return @result
end


then use it like


select dbo.MaskNumbers(contentmail) as maskedcontent from yourtable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources