find first space on text field

[spiff2002]

I need to write a query that will split a text field after the first space found, i.e:
if the contents of the text field is 'red small car 4 door' the query will return 'small car 4 door'
thanks in adavance for any help

 

[ptheriault]

is the datatype a text field or varchar?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Provided it is a varchar, try running these two queries. It should explain things to you. For more info look up CHARINDEX, LEFT, and RIGHT functions in Books online.

If it is text datatype, you will not be able to do this unfortunately.

select 'red small car 4 door' 

select right('red small car 4 door', len('red small car 4 door') - charindex(' ', 'red small car 4 door' ))

Hope it helpsm

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

This will work if it is a Char or Varchar datatype.

SELECT SUBSTRING('red small car 4 door',CHARINDEX(' ','red small car 4 door'),LEN('red small car 4 door'))

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

Can't you convert the text to a char or varchar??

[small]"Mom........MEATLOAF!!!! F***!!!!"[/small]
<.

 

[monksnake]

Here's a converted example (I know it's not text, but it does show a convert)
(tested)
:

select ltrim(rtrim(substring(convert(varchar(50), 'red small car 4 door'), charindex(' ', convert(varchar(100), 'red small car 4 door')), 50)))

[small]"Mom........MEATLOAF!!!! F***!!!!"[/small]
<.

 

[ptheriault]

>>Can't you convert the text to a char or varchar??

But what if the column datatype is TEXT?

won't it get truncated if you try to stuff 5000 characters into a varchar() field.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

I did test that, and that's what I meant column datatype is text.

varchar can hold 8000 and I that would only be an issue if spiff2002's text field data is large enough.

On a side note, I like Alex's query above cause it doesn't use that dirty substring method like mine did.

If that column is a text field, combine Alex and mine's query for Mighty Morphin Query Ranger Number 1.

<--- Not tobacco



[small]"Mom........MEATLOAF!!!! F***!!!!"[/small]
<.

 

[spiff2002]

sorry my bad
it is a varchar field not a text field.
I used ptheriault code and it worked like a charm thank you all.