LIKE? or not - 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 2008 Forums

Transact-SQL (2008)

LIKE? or not

Author

Topic

farid92691
Starting Member

3 Posts

Posted - 2011-08-04 : 12:38:38

Hi,

Scenario: USer enters a 17 char string(Vehicle id number)
I have to find the first matching record with the Most number of matching character in the a table for which I am including sample data.

1. **8S*************
2. **8J***D*********
3. **8S***D*1*******

So if the User enters 'KM8S123DA1E12345' I need to match against record nubmer 3. since it has the most # of matching chars in those positions.

If not found, then match against record 2. and then 1.

What is the most efficent way to do this?

Highly appreciate your input...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 13:02:41

something like


SELECT *
FROM Table
WHERE PATINDEX(REPLACE(Column,'*','%'),@String) >0
ORDER BY LEN(REPLACE(Column,'*','')) DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

farid92691
Starting Member

3 Posts

Posted - 2011-08-04 : 16:34:20

Thank you, however this would get multiple matches..
1. **8S*************
2. **8J***D*********
3. **8S***D*1*******
4. **8S***D*4*******
5. **8S***D*6*******

How can one get the exact match?
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources