Solved

# SQL Query - Search for series of numbers in different form

Posted on 2009-04-27
203 Views
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
Question by:holemania

LVL 3

Expert Comment

Try this:

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

Author Comment

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

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

LVL 17

Expert Comment

Just use this. I am giving only for one query:
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 92

Expert Comment

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

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

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

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

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

Awesome.  Thanks for the help.
0

## Featured Post

### Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…