Returning info up to space?

  • I am having some problems trying to figure this one out. I am trying to write a query to show only the information up to the first space in a field. ie... CONX 6288-4, I only want the "CONX" to be returned. The first part varies between 2,3,or 4 characters before the space. Is this possible. I am able to pull the 6288-4 out using SUBSTRING and CHARINDEX together but I am stumped on the opposite. Any help would be great. Thanks

  • declare @myval as varchar(100)

    set @myval = 'CONX 6288-4'

    select Left(@myval,charindex (' ',@myval,1)-1)


    ------------------------------
    The Users are always right - when I'm not wrong!

  • It makes sense what you put, but I am getting the following error. Any ideas?

    Invalid length parameter passed to the substring function.

  • If any rows do not have spaces, it will object to LEFT(string, -1).

    You could use 

    IF CHARINDEX(' ', @myval)>0 select LEFT(@myval, CHARINDEX(' ', @myval))

    ELSE select ''

     

  • It was the spacing issue. I corrected my data and now it works. Thanks you guys for the help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply