June 28, 2005 at 8:16 am
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
June 28, 2005 at 8:27 am
declare @myval as varchar(100)
set @myval = 'CONX 6288-4'
select Left(@myval,charindex (' ',@myval,1)-1)
June 28, 2005 at 8:52 am
It makes sense what you put, but I am getting the following error. Any ideas?
Invalid length parameter passed to the substring function.
June 28, 2005 at 8:57 am
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 ''
June 28, 2005 at 9:22 am
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