3

I have database with a field 'clinicNo' and that field contains records like 1234A, 2343B, 9999Z ......

If by mistake I use '1234B' instead of '1234A' for the select statement, I want to get a result set which contains clinicNos which are differ only by a one character to the given string (ie. 1234B above)

Eg. Field may contain following values. 1234A, 1235B, 5433A, 4444S, 2978C If I use '1235A' for the select query, it should give 1234A and 1235B as the result.

j0k
  • 22,600
  • 28
  • 79
  • 90
priyanga
  • 39
  • 3

4 Answers4

1

You could use SUBSTRING for your column selection, below example return '1235' with 'A to Z' 

select *  from TableName WHERE SUBSTRING(clinicNo, 0, 5) LIKE '1235A'
Berkay Turancı
  • 3,373
  • 4
  • 32
  • 45
1

What you're looking for is called the Levenshtein Distance algorithm. While there is a levenshtein function in PHP, you really want to do this in MySQL.

There are two ways to implement a Levenshtein function in MySQL. The first is to create a STORED FUNCTION which operates much like a STORED TRANSACTION, except it has distinct inputs and an output. This is fine for small datasets, but a little slow on anything approaching several thousand rows. You can find more info here: http://kristiannissen.wordpress.com/2010/07/08/mysql-levenshtein/

The second method is to implement a User Defined Function in C/C++ and link it into MySQL as a shared library (*.so file). This method also uses a STORED FUNCTION to call the library, which means the actual query for this or the first method may be identical (providing the inputs to both functions are the same). You can find out more about this method here: http://samjlevy.com/2011/03/mysql-levenshtein-and-damerau-levenshtein-udfs/

With either of these methods, your query would be something like:

SELECT clinicNo FROM words WHERE levenshtein(clinicNo, '1234A') < 2;

It's important to remember that the 'threshold' value should change in relation to the original word length. It's better to think of it in terms of a percentage value, i.e. half your word = 50%, half of 'term' = 2. In your case, you would probably be looking for a difference of < 2 (i.e. a 1 character difference), but you could go further to account for additional errors.

Also see: Wikipedia: Levenshtein Distance.

Ian Atkin
  • 6,302
  • 2
  • 17
  • 24
0
SELECT * FROM TABLE
WHERE ClinicNo like concat(LEFT(ClinicNo,4),'%')
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
0

In general development, you could use a function like Levenshtein to find the difference between two strings and it returns you a number of "how similar they are". You probably want then the result with the most similarity.

To get Levenshtein also in MySQL, read this post.

Or just get all results and use the Levenshtein function of PHP.

Community
  • 1
  • 1
Itchy
  • 2,263
  • 28
  • 41