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
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.
Source: Stackoverflow.com