replace and date/month query - 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 and date/month query

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-19 : 12:20:56

SELECT CRDATE, RCTNCCRD
FROM creditcardtable
WHERE (RCTNCCRD <> '') AND (NOT (ISNULL(RCTNCCRD, '999') = '999')) AND (RCTNCCRD NOT LIKE '%x%')
ORDER BY CRDATE DESC

i want to convert any credit card numbers in creditcardtable to an obfuscated format such as:

4332 3423 5423 5428

And convert it to

XXXX XXXX XXXX 5428

HOWEVER, it will only do it if the order is older than 3 months old. Only 90 days/3 months max

how can i do that?
when i use where crcdate<90...it gives me error..
can anyone help me?

KevinKembel
Starting Member

11 Posts

Posted - 2008-03-19 : 12:26:53

I believe you could use where datediff(day,crcdate,getdate()) > 90

http://msdn2.microsoft.com/en-us/library/ms189794.aspx

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-19 : 12:43:44

thanks .. i got it..

now i have to replace that creditcard number...and want to display only last 4 digits.

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-19 : 13:34:04

select rctnccrd,replace(rctnccrd,substring(rctnccrd,1,len(rctnccrd)-4),'xxxxx') as replacement from creditcardtable

i m getting
rctnccrd replacement
---------------------------------
4111111111111111 xxxxx1111
4111111111111111 xxxxx1111
4111111111111111 xxxxx1111
4111111111111111 xxxxx1111
4111111111111111 xxxxx1111
4111111111111111 xxxxx1111
4111111111111111 xxxxx1111
11111111111111111 xxxxx1111

i want xxxxxxxxxxxxxxx1111
except last 4 all x
but don't have to use 'xxxxxxxxxxxxxxxxxxxxxxxx'

palak
Yak Posting Veteran

55 Posts

Posted - 2008-03-20 : 08:41:19

CREATE PROCEDURE CreditCardReplacement

AS

BEGIN

SET NOCOUNT ON

select crdate, rctnccrd as creditcard,

case when (crdate >= dateAdd(m, -3, convert(datetime, convert(varchar(10), getdate(), 112), 112))
AND crdate < dateAdd(d, 1, convert(datetime, convert(varchar(10), getdate(), 112), 112)))
then rctnccrd
else replace(rctnccrd, substring(rctnccrd,1,len(rctnccrd)-4),'xxxx xxxx xxxx ')
end

as replacecreditcard

from creditcardtable

where (RCTNCCRD <> '') AND (NOT (ISNULL(RCTNCCRD, '999') = '999'))

ORDER BY crdate DESC

i want this same select query in update statement...i have to update whole creditcardtable
i have done:
update SOP30201cc

set rctnccrd =

(select

case when (crdate >= dateAdd(m, -3, convert(datetime, convert(varchar(10), getdate(), 112), 112))
AND cr
date < dateAdd(d, 1, convert(datetime, convert(varchar(10), getdate(), 112), 112)))
then rctnccrd
else replace(rctnccrd, substring(rctnccrd,1,len(rctnccrd)-4),'xxxx xxxx xxxx ')
end

as replacecreditcard

)

getting error as :
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

any help would be appreciated.

thanks.

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources