[sql-server] select data up to a space?

I have an MSSQL database field that looks like the examples below:

u129  james
u300  chris
u300a jim
u202  jane
u5    brian
u5z   brian2

Is there a way to select the first set of characters? Basically select all the characters up until the first line space?

I tried messing around with LEFT, RIGHT, LEN, but couldn't figure out a way to do it with variable string lengths like in my example.

Thanks!

This question is related to sql-server tsql

The answer is


select left(col, charindex(' ', col) - 1)

An alternative if you sometimes do not have spaces do not want to use the CASE statement

select REVERSE(RIGHT(REVERSE(YourColumn), LEN(YourColumn) - CHARINDEX(' ', REVERSE(YourColumn))))

This works in SQL Server, and according to my searching MySQL has the same functions


If the first column is always the same size (including the spaces), then you can just take those characters (via LEFT) and clean up the spaces (with RTRIM):

SELECT RTRIM(LEFT(YourColumn, YourColumnSize))

Alternatively, you can extract the second (or third, etc.) column (using SUBSTRING):

SELECT RTRIM(SUBSTRING(YourColumn, PreviousColumnSizes, YourColumnSize))

One benefit of this approach (especially if YourColumn is the result of a computation) is that YourColumn is only specified once.