September 14, 2009 at 7:45 pm
I have a 16digit credit number in a table column which I want to search by last 3-4 digits. ex:
*1234 or * 123 gives records with those suffix digits. Please advice
September 14, 2009 at 9:15 pm
Assuming CC column is a varchar(16) or char(16)..
You could try a LIKE operator
something like
WHERE CCColumn LIKE '____________1234'
That is 12 underscores which in a LIKE operator equals any single character, in other words, we don't care about the first twelve characters.
There is also RIGHT but you take a HUGE processing hit to do it so I'm not going to demonstrate it.
CEWII
September 15, 2009 at 4:15 pm
Elliott W (9/14/2009)
There is also RIGHT but you take a HUGE processing hit to do it so I'm not going to demonstrate it.
I must say I found the complete opposite - I used to manage a loyalty system which had about 14 million card numbers in, and I found that searching with RIGHT(cardnumber, 4) = '1234' gave me much quicker results than LIKE '%1234'.
I don't have the opportunity to test on that data set any more though, so I'd be interested to see how LIKE '____________1234' compares with LIKE '%1234'...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 5:22 pm
I guess we'll have to agree to disagree, my experience has shown the opposite. So the moral of the story is test both, use what works for YOU..
CEWII
September 15, 2009 at 5:40 pm
Elliott W (9/15/2009)
I guess we'll have to agree to disagree, my experience has shown the opposite. So the moral of the story is test both, use what works for YOU..
😀 I'm not actually disagreeing - just saying what I've found in the past... Although...
On a quick test with 3,200,000 rows, the STATISTICS_TIME readings for me are:
[CardNumber] LIKE '%1234':
SQL Server Execution Times:
CPU time = 2813 ms, elapsed time = 2119 ms.
[CardNumber] LIKE '____________1234':
SQL Server Execution Times:
CPU time = 1344 ms, elapsed time = 1732 ms.
RIGHT([CardNumber], 4) = '1234':
SQL Server Execution Times:
CPU time = 1094 ms, elapsed time = 1683 ms.
So yes - for a known-length string, using _ in LIKE is much faster than using %...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 8:55 pm
Fair enough.
CEWII
September 16, 2009 at 9:09 am
Would SUBSTRING work ?
WHERE SUBSTRING(CC#, 13, 4) = '1234'
September 16, 2009 at 9:45 am
Probably, but if you have a 16 character field and you only want the last you and you know there will be data there, it would seem RIGHT is the way to go..
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply