CODEWITHSUNDEEP
sql-servert-sql
SkyeBoniwell
SkyeBoniwell

Reputation: 7122

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!

Upvotes: 58

Views: 212407

Answers (6)

Muskie
Muskie

Reputation: 577

This might appeal to someone it is based on work above:

IIF(charindex(' ', [YouColumn]) = 0, LEFT([YourColumn], LEN([YourColumn])), LEFT([YourColumn], charindex(' ', [YourColumn]) - 1)) AS NewColumnName

Sometime IIF is cleaner than CASE.

Upvotes: 0

user20537514
user20537514

Reputation: 1

If space is missing, you can add one

SELECT LEFT('YourTextOrColumn',
            charindex(' ',
            'YourTextOrColumn' + ' ') - 1 )

Upvotes: -2

camerondm9
camerondm9

Reputation: 1055

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.

Upvotes: 2

rayzinnz
rayzinnz

Reputation: 1958

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

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32730

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)

Upvotes: 130

Blorgbeard
Blorgbeard

Reputation: 103525

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

Upvotes: 3

Related Questions