0

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?

popkutt
  • 949
  • 3
  • 10
  • 19
  • What if the brand name has a number in it? How do you define a word? – Shadow Sep 16 '16 at 12:39
  • Thanks. The brand name never has a number in it. Even if it does it wouldn't be a problem, I can strip these before getting into this task. – popkutt Sep 16 '16 at 12:46
  • or you can set a condition to select a word which has uppercase characters with number.. – Sarath Sep 16 '16 at 13:10
  • There is no built-in function for that. You can use a series of subqueries to get your value, but the easiest (and probably even fastest) way would be to write a stored function that goes through a string char by char and extract the string. – Solarflare Sep 16 '16 at 14:08

1 Answers1

1

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?

Community
  • 1
  • 1
Boris Schegolev
  • 3,601
  • 5
  • 21
  • 34