Trimming a string in sql

Question

SQL

Tell me how to make it so that everything after the space is deleted in the line after the request?
Note. "aaaa bbb" --> "aaaa"

Answer 1

MySQL, for example, has two functions that can help:
- INSTR - returns the first position of a substring in a string.
- LEFT - cuts the text from the beginning to the specified position.
Therefore, you can do this (-1 is needed so that the result does not include a space):

SET @str = 'Некоторая строка с пробелами';
SELECT LEFT(@str, INSTR(@str, ' ') - 1);

But, if there are no spaces in the string, then a zero-length string will be returned. To do this, you can add a condition, and if there are no spaces in the string, return the original string.

SET @str = 'Некоторая_строка_без_пробелов';
SELECT IF(
  INSTR(@str, ' ') = 0, 
  @str,
  LEFT(@str, INSTR(@str, ' ') - 1)
);

PS: But, if you do not need it to compose complex nested queries, then it is better to transfer such processing to the client code.

Answer 2

Trolling - allowed!

Answer 3

Something like this (in MS SQL):

declare @input_str varchar(max) = 'aaa bbb'
         , @idx int
         , @output_str varchar(max)

select @idx = charindex(' ', @input_str)
select @output_str = substring(@input_str, 0, @idx)

Answer 4

It is also convenient to use
SUBSTRING_INDEX(str, delimiter, count): cuts a substring from the string str. The delimiter parameter specifies the delimiter within the string. And the count parameter determines up to which occurrence of the separator the substring should be cut. If count is positive, then the substring is cut from the beginning, if count is negative, then from the end of the string str:
SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', 1), -- Galaxy
(SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ' , 2) ), -- Galaxy S8
(SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', -2) ); -- S8 Plus