I have a table with a varchar, say A, column and the data, for example
I want to create a query that when I search for the data in column A it's give me the amount of different chars (by position).
I mean, if I search for
aacdefghi it will say 1.
If I search for
bcdefghia it will say 9.
the length of the strings will be the same.
Any ideas how to do this?
My table look like:
I think that a script (PHP, Perl, ...) would fit better here, but if you need a MySQL solution you could start with something like this:
SELECT col, COUNT(*) FROM tablename INNER JOIN n ON CHAR_LENGTH(tablename.col) >= n.n WHERE SUBSTRING(tablename.col,n.n,1)!=SUBSTRING('aacdefghi', n.n, 1) GROUP BY col;
(you will need a table n that contains numbers 1..maximum length of the string). Please see fiddle here.
What you are looking for is similar to the Levenshtein distance. You can implement it as stored routine in MySQL, see an example here:
You can modify this for your own needs. It is going to be very slow though, you shouldn't use it on a large data set.