how to mask credit card numbers

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)

how to mask credit card numbers

Author

Topic

phaze
Starting Member

42 Posts

Posted - 2008-08-18 : 01:44:56

I have a column of credit card numbers and i wanted the numbers to be masked. I want the first 4 numbers to be xxxx and the last 4 numbers to be replaced with the number 0

for example

5454008812345555

would become

xxxx008812340000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-18 : 01:48:42

SELECT STUFF(Col1, 1, 4, '****) FROM Table1
SELECT '****' + SUBSTRING(Col1, 5, 12) FROM Table1

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-18 : 01:52:47

[code]declare @cc varchar(20)

select @cc = '5454008812345555'

select stuff(stuff(@cc, 1, 4, 'xxxx'), len(@cc) - 3, 4, '0000')[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-18 : 01:56:32

SELECT '****' + SUBSTRING(Col1, 5, 8) + '0000'

E 12°55'05.25"
N 56°04'39.16"

phaze
Starting Member

42 Posts

Posted - 2008-08-18 : 01:59:35

makes total sense...thank you very much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-18 : 02:09:53

Too much work today. Need to de-stress


declare @cc varchar(20)

select	@cc = '5454008812345555'

select	'XXXX' + right(convert(varchar(20), convert(bigint, @cc) / 10000 * 10000), len(@cc) - 4)

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

hagujju
Starting Member

1 Post

Posted - 2010-01-04 : 16:22:58

declare @cc varchar(20)

select @cc = '5454008812345357'

select replace(space(len(@cc)-4) + right(@cc,4),' ','X')

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-04 : 17:46:49

you're not protecting the card # enough like that. either (1) encrypt it or (2) obfuscate the whole thing, except perhaps the last 4.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-05 : 01:45:15

quote:
Originally posted by hagujju

declare @cc varchar(20)

select @cc = '5454008812345357'

select replace(space(len(@cc)-4) + right(@cc,4),' ','X')

Read the question again

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources