Complex Sql select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex Sql select

Author

Topic

ccbuilder
Starting Member

17 Posts

Posted - 2012-08-15 : 01:16:20

Hello everyone,
I have a table that contains masked values like this
199XX
19911
1XXXX

The X's represent a mask for a wild card in an account, if a user were to query for the following account 19936 the query should return the first row because it contains the most digits that match along with XX characters which are wild carded, it should not return 19911 because 11 at the end do not match 36 so this disqualifies the result.

X's are wild cards.

if account 19811 were searched, then the result would be 1XXXX because the 198 would be the matching pattern.

How can I write a sql query to match such pattern, please note that users will not type wild card accounts, but rather full accounts such as the 19936 example.

I hope this makes sense and thank you in advance for your invaluable help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-15 : 07:20:41

Are the wildcards always to the right? If so something like in the example below?

CREATE TABLE #tmp (x VARCHAR(32));
INSERT INTO #tmp VALUES ('199XX'),('19911'),('1XXXX');

DECLARE @search VARCHAR(32) = '19936';
SELECT * FROM #tmp
WHERE
	LEN(@search) = LEN(x) 
	AND REPLACE(@search,'X','') LIKE REPLACE(x,'X','')+'%'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 13:20:39

Slight simplification, assuming the length's should match:

SELECT * FROM #tmp
WHERE
    @search LIKE REPLACE(x,'X','_')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-15 : 14:36:39

quote:
Originally posted by Lamprey

Slight simplification, assuming the length's should match:
SELECT * FROM #tmp
WHERE
@search LIKE REPLACE(x,'X','_')

Very clever use of underscore indeed!! :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 14:47:59

quote:
Originally posted by sunitabeck

quote:
Originally posted by Lamprey

Slight simplification, assuming the length's should match:
SELECT * FROM #tmp
WHERE
@search LIKE REPLACE(x,'X','_')

Very clever use of underscore indeed!! :)

ccbuilder
Starting Member

17 Posts

Posted - 2012-08-15 : 18:57:21

Yes...sorry for the late reply, i just tested it.
It works like a champ :)

I didn't know that you can use the variable in the 'where' statement in this manner.
I spent so much time looking for a solution, you're a life saver.

Thank you so much for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources