Replace Wildcard Characters - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Replace Wildcard Characters

Author

Topic

esilva002
Starting Member

10 Posts

Posted - 2010-08-20 : 13:53:56

Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2005 table with Social security numbers. We need to get rid of the SSN numbers and replace them with SPLAT (*)
For example:

if the real SSN is: 340-53-7098 the replacement would be ***-**-****.

Sounds simply enough, but I can't seem to get it straight.

I need this to be created using an update query.

Again, any help would be appreciated it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 14:08:52

Create a function like this


CREATE FUNCTION MaskValue
(
@Value varchar(100),
@MaskChar char(1)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @Ret varchar(100)
SELECT @Ret=COALESCE(@Ret,'') + CASE
 WHEN SUBSTRING(i.chr,v.number,1) LIKE '[0-9]' THEN @MaskChar ELSE SUBSTRING(i.chr,v.number,1) END
FROM
(
SELECT @Value AS chr
)i
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND LEN(chr) 

RETURN @Ret
END


Then use it like

SELECT dbo.MaskValue('340-53-7098','*' )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esilva002
Starting Member

10 Posts

Posted - 2010-08-20 : 14:11:46

Would this work if the SSN is not specifically set to '340-53-7098' I am looking to do this to all characters with the SSN pattern.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 14:32:31

didnt get that. it will work as long as you want to replace numeric data with mask character of your choice.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esilva002
Starting Member

10 Posts

Posted - 2010-08-20 : 16:28:33

quote:

Then use it like

SELECT dbo.MaskValue('340-53-7098','*' )

I Don't want to look for a specific ssn (340-53-7098). I'm looking to change anything that looks like it could be a ssn and change it to ***-**-****. I want to use wildcards!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 04:23:10

you can still use like

SELECT dbo.MaskValue(yourssnfieldhere,your desired mask character here) FROM YourTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-23 : 03:01:36

or use multiple replaces

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
col,'0','*'),'1','*'),'2','*'),'3','*'),'4','*'),'5','*'),'6','*'),'7','*'),'8','*'),'9','*')
from your_table

A similar approch is at
http://beyondrelational.com/blogs/madhivanan/archive/2010/07/19/initcap-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources