CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

MySql Trimming characters till a space

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?

Upvotes: 1

Views: 406

Answers (2)

Sparky
Sparky

Reputation: 15115

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

Upvotes: 2

har07
har07

Reputation: 89325

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

Upvotes: 2

Related Questions