MySql Trimming characters till a space

Question

How can I Trim (remove) all characters from the Right of a string upto the first space?

Here's what I'm trying to do:

set @s:='May the Gods watch over your battles, my friend!';

select if(length(@s) < 10,@s,left(@s,10)) a;

#output from above: 'May the Go'

#desired output: 'May the'

To avoid such odd outputs like May the Go I'm trying to trim all characters from the right, upto the first space, so the output is May the.

How can this be done in the sql statement itself. I could not find a built in function that'll do this?

Sparky · Accepted Answer · 2014-09-14T09:31:40.363

2

This works in Microsoft SQL, it should work if you replace CHARINDEX with INSTR

select substring(@s,1,charindex(' ',reverse(@s)))

Added a my SQL fiddle version below, works a bit different than in Microsoft SQL

http://sqlfiddle.com/#!2/d41d8/44718

select @s,left(@s,10-locate(' ',reverse(@s)));

Example within the database

select theFld, 
CASE    
     WHEN  length(theFld) <= 20 THEN theFld  
     ELSE
        left(theFld,20-locate(' ',reverse(left(theFld,20))))   
     END  as Abbr 
FROM example;

See this SQL fiddle : http://sqlfiddle.com/#!2/beac7/6

edited Sep 14 '14 at 09:31

answered Sep 13 '14 at 11:58

Sparky

14,967
2
31
45

Nothing. Just returns a blank. – Norman Sep 13 '14 at 13:10
Ah! Locate is MySql's equivalent of charindex (or at least is what works here) – Norman Sep 13 '14 at 15:08
Your solution uses 'reverse', but still outputs data in the right direction. How does it do that? – Norman Sep 14 '14 at 04:36
So, I did it like this (using the table I'll be using): `select if(length(string) > 20,concat(left(string,20-locate(' ',reverse(string))),'>>>'),string) a from string;` Hope I'm not doing anything incorrectly. Thanks – Norman Sep 14 '14 at 06:11
Guess I spoke too soon. Added it to the main app, and discovered it wont work. How do I build the sql using my actual table and it's columns? – Norman Sep 14 '14 at 07:22
See revised answer and new fiddle – Sparky Sep 14 '14 at 09:33

score 2 · Answer 2 · edited May 23 '17 at 10:33

2

You can try this way :

.....
.....
--define max length for the extracted text
set @length:=10;
set @result = 
          --check if either the last extracted character or... 
          --the character next to the last is a space (or both are spaces)  
          if(substring(@s, @length, 2) LIKE '% %',  
          --if true, extract using simple left()'
            left(@s,@length),
          --else, use the same simple left() operation then remove all characters.. 
          --starting from the right-most until the first space found
            replace(left(@s,@length), substring_index(left(@s, @length), ' ', -1), '')
          );

[SQL Fiddle demo]

For reference : MySQL String Last Index Of

edited May 23 '17 at 10:33

Community

1
1

answered Sep 13 '14 at 14:37

har07

88,338
12
84
137

Works well, but is a overkill :-) – Norman Sep 13 '14 at 15:41
which part do you think is overkill? I agree that you may be able to use `substring()` with `locate()` instead of `replace()`, but the `if()` check is still necessary as far as I can see. For instance in a case when `@length:=7` – har07 Sep 13 '14 at 16:11
I don't think it is overkill, he added a lot comments to show what is going on in the code. My SQL-Server example in my answer is shorter, but has no comments. – Sparky Sep 13 '14 at 16:14

MySql Trimming characters till a space

2 Answers2