sql-servert-sql

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!

Solution

  • You can use a combiation of LEFT and CHARINDEX to find the index of the first space, and then grab everything to the left of that.

     SELECT LEFT(YourColumn, charindex(' ', YourColumn) - 1)

    And in case any of your columns don't have a space in them:

    SELECT LEFT(YourColumn, CASE WHEN charindex(' ', YourColumn) = 0 THEN 
    LEN(YourColumn) ELSE charindex(' ', YourColumn) - 1 END)