?
Solved

SQL Query - Search for series of numbers in different form

Posted on 2009-04-27
10
Medium Priority
?
208 Views
Last Modified: 2012-05-06
I need to search for credit card numbers in a user_defined field for multiple purposes.  However, what makes it a little complicated is that sometimes these credit card number come in 4 digits with a hyphen, space, or no space.

Example:
1234-5678-9012-4567
1234 5678 9012 4567
12345678901234567

Is there any way to query it so that it looks for any number in that type of series?

0
Comment
Question by:holemania
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Expert Comment

by:GSQL
ID: 24243635
Try this:

select replace(replace(CreditCardNumber, ' ', ''),'-','') as CCNum from YourTable
0
 

Author Comment

by:holemania
ID: 24243847
Here's the problem.  That user_defined field have other notation in there.

Example:
CC INFO: 1234 5678 9012 3456 -JOE

Or something like:
CREDIT CARD: 1234-5678-9012-3456 EXP 12/05   -CREATED BY BOB

So I need to create a where clause where it would look for series of numbers following those formats.
0
 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 400 total points
ID: 24243865
For searching well within each series, you can use 3 separate queries:
SELECT CreditCardNumber FROM YourTable WHERE SUBSTRING(CreditCardNumber,5,1) = '-'; -- Hyphen
SELECT CreditCardNumber FROM YourTable WHERE SUBSTRING(CreditCardNumber,5,1) = ' '; -- Empty space
SELECT CreditCardNumber FROM YourTable WHERE SUBSTRING(CreditCardNumber,5,1) BETWEEN '0' AND '9'; -- For continuous number
You can use a UNION to combine 2 or all three types of data i.e.
SELECT CreditCardNumber FROM YourTable WHERE SUBSTRING(CreditCardNumber,5,1) = '-'
UNION
SELECT CreditCardNumber FROM YourTable WHERE SUBSTRING(CreditCardNumber,5,1) = ' '
UNION
SELECT CreditCardNumber FROM YourTable WHERE SUBSTRING(CreditCardNumber,5,1) BETWEEN '0' AND '9';

You will get the results sorted since UNION tries and removes duplicates if any are found. If you want duplicates as well, use UNION ALL.

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24243898
Just use this. I am giving only for one query:
GSQL, for your post:
select SUBSTRING(replace(replace(CreditCardNumber, ' ', ''),'-',''),1,16) as CCNum from YourTable;
For mine:
SELECT SUBSTRING(CreditCardNumber,1,16) FROM YourTable WHERE SUBSTRING(CreditCardNumber,5,1) = '-';
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24243902
Hello holemania,

Since you are working in SQL 2005, you can create a C# powered UDF using Regular Expressions.  I am not
a C# guy, but I would use a pattern such as:

      \d[^a-zA-Z]+\d

and grab the first match out of the entry.

Regards,

Patrick
0
 
LVL 3

Expert Comment

by:GSQL
ID: 24243940
do you mean like this:

declare @cctbl table (CreditCardNumber nvarchar(100))
insert @cctbl values ('CREDIT CARD: 1234-5678-9012-3456 EXP 12/05   -CREATED BY BOB')
insert @cctbl values ('CC INFO: 1234 5678 9012 3456 -JOE')
insert @cctbl values ('abc')

select * from @cctbl where CreditCardNumber like '%[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%'


0
 

Author Comment

by:holemania
ID: 24244065
Yes, I think GSQL nailed it with that last one.  So is that looking for any digits as long as it's 16 digits regardless if there's space, hyphen, or nothing?  Seems to be pulling it now, just confirming it.

Like what I said there are other notations in there such as claim numbers which it doesn't seem to be pulling.  Like claim#8898922 or phone number.  
0
 
LVL 3

Accepted Solution

by:
GSQL earned 1600 total points
ID: 24244092
declare @cctbl table (CreditCardNumber nvarchar(100))
insert @cctbl values ('CREDIT CARD: 1234-5678-9012-3456 EXP 12/05   -CREATED BY BOB')
insert @cctbl values ('CC INFO: 1234 5678 9012 3456 -JOE')
insert @cctbl values ('abc')


select replace(replace(substring(CreditCardNumber, patindex('%[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%', CreditCardNumber), 20),' ',''),'-','') as CC
from @cctbl
where CreditCardNumber like '%[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%'

0
 
LVL 3

Expert Comment

by:GSQL
ID: 24244117
Sorry - I didn't read your last post before I reposted.  

The latter soltuion will return just the 16 digit cc number, no hyphens, no spaces.  Note that i used 20 characters in the substring.  You may need to increase if there are more than 4 additional characters in the ccnumber text.

0
 

Author Comment

by:holemania
ID: 24244220
Awesome.  Thanks for the help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question