select data up to a space

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

select left col  charindex      col  - 1

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

Examples related to sql-server

Examples related to tsql