Finding one of an array of tokens in a particular string position 2

[spamjim]

I could use some guidance to select all products where the 2nd position of the string is equal to P or J or W or Z or G or any of another dozen defined letters. Is there a way to do this without just copying/pasting the LOCATE for each valid option in this example?

SELECT DISTINCT `Type` 
FROM products WHERE LOCATE("P", `Type`) = '2' OR LOCATE("J", `Type`) = '2' 
ORDER BY `Type`

This could get clunky:
...OR LOCATE("P", `Type`) = '2'
OR LOCATE("J", `Type`) = '2'
OR LOCATE("W", `Type`) = '2'
OR LOCATE("Z", `Type`) = '2'
OR LOCATE("G", `Type`) = '2'...

Is there a better way?

 

[feherke]

Hi

Somehow similar :

[b]SELECT[/b] [b]DISTINCT[/b] `Type`

[b]FROM[/b] products

[b]WHERE[/b] substring[teal]([/teal]`Type`[teal],[/teal] [purple]2[/purple][teal],[/teal] [purple]1[/purple][teal])[/teal] [b]in[/b] [teal]([/teal][i][green]'P'[/green][/i][teal],[/teal] [i][green]'J'[/green][/i][teal],[/teal] [i][green]'W'[/green][/i][teal],[/teal] [i][green]'Z'[/green][/i][teal],[/teal] [i][green]'G'[/green][/i][teal])[/teal]

[b]ORDER[/b] [b]BY[/b] `Type`

Note that this is not exactly the same as your original. This will return true for a `Type` value of 'PP-blah', while your original returns false in this case. See whether this is an issue for you.

Feherke.
feherke.ga

 

[spamjim]

Thanks! My original sample would indeed give me an unwanted result for 'PP-blah'.

I don't know what insanity led me to tinker with locate as I should have stuck to my original attempts with substring.

 

[jpadie]

you are also be able to use regex. but with simple comparisons I doubt whether this would give you any performance benefit over the substring method.

SELECT DISTINCT `Type`
FROM products
WHERE `Type` REGEXP '^.[PJWZG].*$'
ORDER BY `Type` ASC