CODEWITHSUNDEEP
mysqlsubstring
PepperoniPizza
PepperoniPizza

Reputation: 9102

Mysql matching one or more spaces

I have a string column named "matches" that has data like this:

054T,Arequipa,Peru

I am using a query for extracting a substring, for example extracting the first appearing term:

SUBSTRING_INDEX(matches, ',', 1)

that succesfully returns 054T. Now the problem is that I have data that is separated by one or more spaces:

054T     Arequipa    Peru

and:

SUBSTRING_INDEX(matches, ' ', 1)

does not return 054T, what would be the regex like dilimiter for one or more spaces ?

Thanks

Upvotes: 0

Views: 523

Answers (1)

raina77ow
raina77ow

Reputation: 106385

This should do the trick:

SELECT LEFT(matches, LOCATE(' ', matches) - 1);

LOCATE returns an index (1-based) of the first whitespace symbol, and with LEFT you just take that number of symbols from your string's left side.

And this snippet will return the value itself when there's no whitespace in it:

SELECT IF(
  LOCATE(' ', matches) != 0, 
  LEFT(matches, LOCATE(' ', matches) - 1), 
  matches
);

And if, as you mentioned, there can be both spaces and tabs, just prepare the string accordingly:

SELECT LEFT(matches, LOCATE(' ', REPLACE(matches, '\t', ' ')) - 1);

Still I wonder, can't you just normalize it:

UPDATE your_table SET matches = REPLACE(matches, '\t', REPEAT(' ', 4));

... or, perhaps better, just move it into another table (as it really looks like a set in disguise at present).

Upvotes: 1

Related Questions