June 17, 2009 at 7:35 am
hi, say I have a field [text] with the data 'Jamie's Car'
how can I show just the word 'Jamie's'
?
June 17, 2009 at 7:57 am
Something like the following should do it:
declare @SearchChar char(1)
set @SearchChar = ' '
declare @Name varchar(50)
set @Name = 'Jamie''s Car'
select left(@Name, charindex(@SearchChar, @Name) - 1)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2009 at 7:58 am
thats great thanks.
June 17, 2009 at 9:06 am
Dude, where's your car? (Sorry, couldn't resist. :-P)
You might also want to check out PATINDEX if you have multiple possible delimiters.
June 17, 2009 at 9:35 am
select Substring(Name,CHARINDEX(' ', Name)+1,34)
June 18, 2009 at 12:51 am
Try this....
SELECT [Text],LEFT([Text],CASE WHEN CHARINDEX(' ',[Text])-1 >0 THEN CHARINDEX(' ',[Text])-1 ELSE LEN([Text]) END) FROM TABLENAME
June 18, 2009 at 2:07 am
slange (6/17/2009)
Something like the following should do it:
declare @SearchChar char(1)
set @SearchChar = ' '
declare @Name varchar(50)
set @Name = 'Jamie''s Car'
select left(@Name, charindex(@SearchChar, @Name) - 1)
There is a potential error in this script. If @Name does not have any ' ' then, the following error would raise:
'Invalid length parameter passed to the SUBSTRING function.'
June 19, 2009 at 9:47 am
Fixing the bugs an exercise for the student? 🙂
With similar needs I've been caught out where I'd prefer to include more than one short first word, such as "St" or "New" or even "East" and "West". But I'm not sure what's a general nice answer to that. Start after character five or six or seven (gotta figure "North" and "South" too), or have a list of meaningless words that aren't gonna count... this could require a function.
By the way, [text] - really? Actually, I am just now moving into SQL Server 2005, so I have a lot of data in [text] and I don't really know what [varchar](MAX) can do for me. One thing it can do is avoid a need to use TEXTPTR() in a view, which is now only allowed on database compatibility setting 80. Maybe it also can avoid some of the maniacs that I work with defining varchar(7800) after apparently calculating just how many bytes can be fitting in a table row. And then setting one value to the whole length as a test. Fine until someone else wants to add a data column too.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply