Get All Characters Before Space in MySQL

Get all characters before space in MySQL

SELECT LEFT(field1,LOCATE(' ',field1) - 1)

Note that if the string in question contains no spaces, this will return an empty string.

MySQL get all characters before specific character

you need the following statement to get that portion of [ColName]:

LEFT([ColName],INSTR([ColName],"|")-1)

If you want to select multiple columns into the same recordset column you can union all with something like the following:

 SELECT LEFT(ColName,INSTR(ColName,"|")-1) AS FirstValue From $TableName;
 UNION ALL
 SELECT LEFT(ColName2,INSTR(ColName2,"|")-1) AS FirstValue From $TableName;

If you want to use this on multiple columns, script the creation of the sql.

MySQL get all characters before specific character, If not then whole value

You can use SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX(movie, '.', 1) movie
FROM movie_list;

How to get all words after first white space in mysql query

You can use either (or both) SUBSTRING and LOCATE, or SUBSTRING_INDEX. Both of them are used below:

SELECT
  SUBSTRING_INDEX(`name`, ' ', 1) AS `first_name`,
  SUBSTRING(`name`, LOCATE(' ', `name`)) AS `last_name`
FROM
  `names`

Gives us:

first_name | last_name
----------------------
NIWEMUKIZA | GRACE
NIYIRORA   | MARY REINE
Muganga    | jean ema samuel

SQLFiddle: http://sqlfiddle.com/#!9/92e94e/5

How to return the string after the first space in MySQL

Use a combination of SUBSTR() and INSTR()

SUBSTR("This is my life" FROM (INSTR("This is my life", " ") + 1))

Select all characters in a string until a specific character MySQL

Use SUBSTRING_INDEX.

select player_name,substring_index(stats,':',1) 
from T1

Edit: Splitting the values between the first 3 : delimiters,

select player_name
,substring_index(stats,':',1) as col_1
,substring_index(substring_index(substring_index(stats,':',3),':',-2),':',1) as col_2
,substring_index(substring_index(stats,':',3),':',-1) as col_3
from T1

Get everything before and after a character

If string between the first two slashes will not start with an integer whatsoever and the string after the first 2 slashes is always going to start with an integer then below is the solution.

declare @tbl table(string varchar(200))

insert into @tbl
values('AX/Aland Island No.5/20142.87/20542.87')
,('KR/Republic of Korea/19-02-2022/19-03-2022')

select SUBSTRING(string,PATINDEX('%[/][0-9]%',string)+1,
CHARINDEX('/',SUBSTRING(string,PATINDEX('%[/][0-9]%',string)+1,len(string)))-1)
from
@tbl

How to get everything before the last occurrence of a character in MySQL?

Here is one trick do this, making use of the REVERSE function:

UPDATE yourTable
SET col =
    REVERSE(SUBSTRING(REVERSE(col), INSTR(REVERSE(col), '.')))

Demo

The idea here is to reverse the string, then use INSTR to find the position of the last (now first) dot. Then, we substring from the position to the beginning, and then reverse again.

Select Two Words before character in string in mysql

If you just need to select the word just before : and the words separated by a space then you can use substring_index as

select 
 substring_index(
   substring_index('bla bla bla Favorite Color: Red yada yada',':',1)
   ,' ',-1
 ) as w ;

And if you want Favorite Color then change the index from -1 to -2

SQL: how to pull characters before the first space

From reading the documentation of the netezza string functions, I believe that you should be able to achieve this by using functions instr() and substr(), like :

substr(
    my_column, 
    1,
    instr(my_column, ' ') - 1
)

Basically, instr(my_column, ' ') gives you the position of the first space in the string ; substract 1 from it and you get the number of characters that you want to capture at the beginning of the string.

If given a NULL value, this expression should return NULL as well.

---

Related Topics