November 18, 2010 at 12:56 pm
Have a table with CardNumber(nvarchar(60),null).
I'm Writing a search proc where last four digits is optionally passed in as @CardNumber. I'm trying to do a Like ('%@CardNumber') but it has to allow for nulls on both sides as the column itself is nullable. Kind of what I'm doing with LastName and PNREF below that have the same attributes except I'm looking for a like or some thing to the effect of Ends with @Cardnumber. I'm on SQL 2008. Thanks!
SELECT
T.[TransID], T.[FirstName], T.[LastName],
T.[AreaCode], T.[Prefix], T.[Suffix], T.[CardType], T.[CardNumber],
T.[PaymentAmount], T.[Date], T.[MerchantID], T.[RESPMSG], T.[Recurring],T.[PaymentReason]
WHERE T.[RESULT] = '0'
AND (T.[Date] >= @startDate AND T.[Date] < @endDate)
AND COALESCE(T.[LastName],'') = COALESCE(NULLIF(@lastName,''), T.[LastName],'')
and Coalesce(T.[PNREF],'') = COALESCE(Nullif(@ExtReference,''),T.[PNREF],'')
November 18, 2010 at 1:18 pm
First, I sincerely hope you aren't talking about credit card numbers. Whatever you do, don't store those unencrypted! Again, DON'T store credit card numbers in plain-text. If you do, the company you are working for is just asking for legal troubles of the sort that usually result in going out of business. I don't know if that's what you're asking about, but it sure looks like it.
If you need the last four from credit cards, store that in a separate column by itself. Then you don't have to worry about using like or whatever, just use an equality test. Then store the rest of the card number hashed, if you need to store it at all.
You can use the same concept for other types of cards, but if it's actually safe to store the number in plain-text, you can use a calculated column that pulls the right 4 digits. Take a look at the Right() function for that. You can use equality tests on that column and you'll get better query efficiency that way, if you index the column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 2:10 pm
What company do you work for? I want to make sure I don't buy anything from them with a credit card....
The probability of survival is inversely proportional to the angle of arrival.
November 18, 2010 at 2:40 pm
Funny. i remember those days. Not sure whats worst. Not encripting credit card numbers or posting that fact accross the world. you would be very wise not to give your company name. May want to check ur profile and remove it if it's in there.
November 18, 2010 at 3:00 pm
hehe yeah. The early internet days... when there were no hackers... credit card numbers you entered on straight http forms got sent back to the server as a query string.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply