How to select a substring up to Nth space based on the fixed character in SQL Server and Oracle SQL

advertisements

I need to select a substring based on fixed character length till Nth space. Let me explain the problem.

Let's assume I have three different strings:

  1. Lorem ipsum dolor sit amet, consectetur adipiscing elit
  2. Lorem ipsumdolor sit amet, consectetur adipiscing elit
  3. Lorem ipsum dolorsitamet, consectetur adipiscing elit

If I select 20 character from the beginning of the string I get the following substring sequentially

  1. Lorem ipsum dolor si
  2. Lorem ipsumdolor sit
  3. Lorem ipsum dolorsit

But I want my substring (which is at most 20 characters long) like this

  1. Lorem ipsum dolor
  2. Lorem ipsumdolor sit
  3. Lorem ipsum

That is, I do not want any partial word between two whitespaces

Please help me to generate the query.


Oracle:

select substr(substr(MyField,1,20), 1, instr(substr(MyField,1,20), ' ',-1,1))
from MyTable

SQL Server

SELECT LEFT(MyField, 20 - CHARINDEX (' ' ,REVERSE(LEFT(MyField,20))))
FROM MyTable