question

Sam123 avatar image
Sam123 asked

using SUBSTRING to find the middle word in a sentence

I have a column where the rows are set like this

healthy eating habits

healthy working conditions

healthy snacks delicious

I need to extract the middle words only (eating,working,snacks)

I'm not sure how to put the syntax together, so far what I have is

SELECTSUBSTRING([Column3],CHARINDEX(' ', [Column3]),LEN([Column3])- CHARINDEX(' ', [Column3]))

can you please help? thanks

sql server 2012substring
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

You need to find the position of the second ' ' - do this by SUBSTRINGing the original string from the position of the first ' ', and then using CHARINDEX again

select substring(
  [Column3],
  charindex(' ', [Column3])+1,
  charindex(' ',substring([Column3],CHARINDEX(' ', [Column3])+1,LEN([Column3]))))
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.