popkutt popkutt - 1 year ago 82
MySQL Question

How to extract a word(s) with digits from a string in MySQL?

Sample text:

Calvin Klein K2R2S1K6 Women

I need to get the K2R2S1K6 of this example, it could be at any position in the string. Is it possible to get the first word with digits in it?

Answer Source

Could be something like:

SELECT REGEXP_REPLACE('Calvin Klein K2R2S1K6 Women', '^(.*?)([^ ]*[0-9][^ ]*).*$', '\2')

I could only test it against PostgreSQL as I don't have MySQL at hand, so it may need minor tweaking. The regex is POSIX, though, so generally should work just like that.

Also, look here for some MySQL regex info: How to do a regular expression replace in MySQL?