match part of a string 2

[MikeM2468]

I need to perform a query that will check to see if an entry matches part of a string. For example, if the string is abcd1234, I need to find any entries that match any four consecutive characters in the string (e.g. bcd1 or d123).

Is this possible?

 

[towerbase]

Googling

MySQL match part of string

brought up the answer for me at

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Andrew

 

[r937]

unfortunately, andrew, that page you cited does not include the functions that are required for this problem -- at least, if i have understood the problem correctly

SELECT * 
  FROM daTable
 WHERE INSTR('abcd1234',daColumn) > 0 
   AND LENGTH(daColumn) = 4

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MikeM2468]

Thanks for this. But tell me if you had one thing backwards?

WHERE INSTR('abcd1234',daColumn)

should be

 WHERE INSTR(daColumn,'abcd1234')

This will match the string, but I think the LENGTH = 4 part might not be right. That's checking the length of the string, not checking that 4 consecutive characters are matched. If I have abcd1234 and zycd1256, I want it to see that cd12 is matched.

 

[r937]

mike, now you are confusing me

i thought you wanted to submit a string, e.g. 'abcd1234', and find those rows where the value in a certain column matched at least 4 of the characters in the submitted string

perhaps you could give some detailed examples, making sure you distinguish between which is the column in the table and which is the string

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MikeM2468]

To simplify the scenario, I have one table called Table1. Columns are User and Password
User Password
Joe abcd1234
Bill wxyz5678

When I update the password field, I want to check that the new password doesn't match any 4 consecutive characters in the old one. I won't be using encryption in this scenario.

 

[r937]

check that the new password doesn't match any 4 consecutive characters in the old one"

the passwords you've shown are 8 characters, so how could an 8-character password match a 4 character string?

please give more comprehensive examples

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MikeM2468]

Maybe I should have worded it as "check that no 4 consecutive characters in the new password match any 4 consecutive characters in the old one". Sometimes the brain doesn't recognize its own ambiguity.

I don't want to only check to see if the new password matches the old one - especially since the passwords can be of random length. I want to check to see if a part of the password matches a part of the old one. If Joe's password is abcd1234, and it changes to zbcd1567 it should find a match on bcd1. If it changes from abcd1234 to abcd5678, it should match on abcd. If it changes from abcd1234 to wxabcd78 it should match on abcd. If it changes from abcd1234 to abcz5234 it shouldn't match anything.

 

[r937]

okay, now i think i get it ;-)

since there are five 4-character substrings inside an 8-character string, you will need to use ORs to combine five separate INSTR functions

WHERE INSTR(newpassword,SUBSTRING(oldpassword,1,4) > 0
   OR INSTR(newpassword,SUBSTRING(oldpassword,2,4) > 0
   OR INSTR(newpassword,SUBSTRING(oldpassword,3,4) > 0
   OR INSTR(newpassword,SUBSTRING(oldpassword,4,4) > 0
   OR INSTR(newpassword,SUBSTRING(oldpassword,5,4) > 0

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MikeM2468]

OK. How would this work with a 20 character password? A lot more ORs? Is REGEX an option?

 

[r937]

not a lot more ORs, just a few more ;-)

regex is not an option

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MikeM2468]

I found that if I put too many ORs, it returns false positives. I'll do some PHP magic to get the LENGTH first and then use WHILE to create the required number of ORs.

 

[feherke]

Hi

Personally I would prefer another approach. Because despite that the maximum common substring length is 4, if there is a longer one, I would like to show that in the error message. For example in case of 'zycd12356' vs. 'abcd1234' the longest common part is 'cd123'.

For a complex checking like that, you can write a stored procedure :

delimiter $$

[b]create[/b] [b]function[/b] partialmatch[teal]([/teal]one [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal]),[/teal]two [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal]))[/teal]
returns [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal])[/teal]
deterministic
no sql
[b]begin[/b]
  [b]declare[/b] len1 [maroon]int[/maroon] [b]default[/b] length[teal]([/teal]one[teal]);[/teal]
  [b]declare[/b] len2 [maroon]int[/maroon] [b]default[/b] length[teal]([/teal]two[teal]);[/teal]
  [b]declare[/b] len [maroon]int[/maroon] [b]default[/b] len1[teal];[/teal]
  [b]declare[/b] pos [maroon]int[/maroon][teal];[/teal]
  [b]declare[/b] sub [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal]);[/teal]

  [b]while[/b] len[teal]>[/teal][purple]0[/purple] [b]do[/b]
    [b]set[/b] pos[teal]=[/teal][purple]1[/purple][teal];[/teal]
    [b]while[/b] pos[teal]<=[/teal]len1[teal]-[/teal]len[teal]+[/teal][purple]1[/purple] [b]do[/b]
      [b]set[/b] sub[teal]=[/teal]substring[teal]([/teal]one[teal],[/teal]pos[teal],[/teal]len[teal]);[/teal]
      [b]if[/b] instr[teal]([/teal]two[teal],[/teal]sub[teal])!=[/teal][purple]0[/purple] [b]then[/b]
        [b]return[/b] sub[teal];[/teal]
      [b]end[/b] [b]if[/b][teal];[/teal]
      [b]set[/b] pos[teal]=[/teal]pos[teal]+[/teal][purple]1[/purple][teal];[/teal]
    [b]end[/b] [b]while[/b][teal];[/teal]
    [b]set[/b] len[teal]=[/teal]len[teal]-[/teal][purple]1[/purple][teal];[/teal]
  [b]end[/b] [b]while[/b][teal];[/teal]

  [b]return[/b] [b]null[/b][teal];[/teal]
[b]end[/b][teal];[/teal]
$$

delimiter [teal];[/teal]

When called like this :

[b]select[/b] partialmatch[teal]([/teal]password_field[teal],[/teal][green][i]'new_password'[/i][/green][teal])[/teal] [b]from[/b] user_table [b]where[/b] id[teal]=[/teal][purple]42[/purple][teal];[/teal]

it returns the first longest common substring. If no common substring is found, returns [tt]null[/tt].


Feherke.

http://free.rootshell.be/~feherke/

 

[MikeM2468]

This seems like a nice method. What would I need to do to get it return a numeric value for the number of consecutive characters that match?

 

[feherke]

Hi

What would I need to do to get it return a numeric value for the number of consecutive characters that match?

The simplest is to get the returned value's length :

[b]select[/b] [highlight]coalesce[teal]([/teal]length[teal]([/teal][/highlight]partialmatch[teal]([/teal]password_field[teal],[/teal][green][i]'new_password'[/i][/green][teal])[/teal][highlight][teal]),[/teal][purple]0[/purple][teal])[/teal][/highlight] [b]from[/b] user_table [b]where[/b] id[teal]=[/teal][purple]42[/purple][teal];[/teal]

Note that if [tt]null[/tt] is also good for you, you can ommit the use of [tt]coalesce()[/tt].

Or you can rewrite the function to return numeric :

delimiter $$

[b]create[/b] [b]function[/b] partialmatch[teal]([/teal]one [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal]),[/teal]two [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal]))[/teal]
returns [highlight][maroon]int[/maroon][/highlight]
deterministic
no sql
[b]begin[/b]
  [b]declare[/b] len1 [maroon]int[/maroon] [b]default[/b] length[teal]([/teal]one[teal]);[/teal]
  [b]declare[/b] len2 [maroon]int[/maroon] [b]default[/b] length[teal]([/teal]two[teal]);[/teal]
  [b]declare[/b] len [maroon]int[/maroon] [b]default[/b] len1[teal];[/teal]
  [b]declare[/b] pos [maroon]int[/maroon][teal];[/teal]
  [b]declare[/b] sub [maroon]varchar[/maroon][teal]([/teal][purple]100[/purple][teal]);[/teal]

  [b]while[/b] len[teal]>[/teal][purple]0[/purple] [b]do[/b]
    [b]set[/b] pos[teal]=[/teal][purple]1[/purple][teal];[/teal]
    [b]while[/b] pos[teal]<=[/teal]len1[teal]-[/teal]len[teal]+[/teal][purple]1[/purple] [b]do[/b]
      [b]set[/b] sub[teal]=[/teal]substring[teal]([/teal]one[teal],[/teal]pos[teal],[/teal]len[teal]);[/teal]
      [b]if[/b] instr[teal]([/teal]two[teal],[/teal]sub[teal])!=[/teal][purple]0[/purple] [b]then[/b]
        [b]return[/b] [highlight]len[/highlight][teal];[/teal]
      [b]end[/b] [b]if[/b][teal];[/teal]
      [b]set[/b] pos[teal]=[/teal]pos[teal]+[/teal][purple]1[/purple][teal];[/teal]
    [b]end[/b] [b]while[/b][teal];[/teal]
    [b]set[/b] len[teal]=[/teal]len[teal]-[/teal][purple]1[/purple][teal];[/teal]
  [b]end[/b] [b]while[/b][teal];[/teal]

  [b]return[/b] [highlight][purple]0[/purple][/highlight][teal];[/teal]
[b]end[/b][teal];[/teal]
$$

delimiter [teal];[/teal]

Feherke.

http://free.rootshell.be/~feherke/

 

[MikeM2468]

I can work with this. Thanks.