0

Given a list of 6-digit strings (representing a portion of a Social Security Number), I need to pull back a dataset of users whose SSN matches one of those strings. My SQL is: 

SELECT DISTINCT
         u.ssn,
         u.name
FROM user u
WHERE (u.ssn LIKE '%111111%' OR u.ssn LIKE '%222222%')

I'd like to use a prepared statement instead of generating inline SQL (injection attacks, etc.) Is there a way I can get my data back without creating inline SQL?

There are similar questions on this site, but the problem in my case is that using an IN clause instead of LIKE is not an option. I'm only given 6 digits and have to search for the entire 10-digit SSN.

p.s. This is a .Net application with a SQL Server back end.

Community
  • 1
  • 1
Vishal Bardoloi
  • 652
  • 5
  • 18
  • 1
    Could always prepare your statement for binding and just execute it for each ssn: ...Where u.ssn LIKE '%' || :ssn || '%' – crowebird Nov 28 '12 at 22:59

1 Answers1

2

Put your search terms in a table and join them:

declare @SSN table (SSN char(9))
insert into @SSN select '123456789' union select '456789321' union select '789123456'

declare @SearchString table (SearchString varchar(9))
insert into @SearchString select '893' union select '9123'

select ssn.SSN
from @SSN ssn
join @SearchString sst on ssn.SSN like '%' + sst.SearchString +'%'

If performance is an issue, and if your search strings are always at least 5 characters long (for example), you could create a table with each SSN and every possible 5-character substring. Then you could join the columns on matching values instead of partial values. But again, that depends on your requirements.

Pondlife
  • 15,992
  • 6
  • 37
  • 51