CODEWITHSUNDEEP
sqlsql-serverregexoracle-database
Alfred Ace
Alfred Ace

Reputation: 113

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

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.

Upvotes: 2

Views: 904

Answers (3)

user5683823
user5683823

Reputation:

For Oracle (it should be possible to translate this to SQL Server, but I don't know SQL Server:

  • If the first "token" (before the first space) is more than 20 characters, then this will return NULL
  • if the 21st character is a space, return the first 20 characters
  • if the 21st character is not a space, but there is a space among the first 20 characters, then chop off the first 20 characters, and then find the "last" space and delete it and everything after it.
  • If the whole string is at most 20 characters, it should be returned as is.

In the test data below I added two more examples to test if this is working as needed.

with
     inputs ( str ) as (
       select 'Lorem ipsum dolor sit amet, consectetur adipiscing elit' from dual union all
       select 'Lorem ipsumdolor sit amet, consectetur adipiscing elit'  from dual union all
       select 'Loremipsumdolorsitametconsedtetur'                       from dual union all
       select 'Lorem ipsumdolorsit amet, consectetur etc.'              from dual union all
       select 'Lorem ipsum dolorsitamet, consectetur adipiscing elit'   from dual union all
       select 'abcdef ghijk lmno'                                       from dual
     ),
     prep ( str, flag, fragment ) as (
       select str, 
              case when length(str) <= 20 or substr(str, 21, 1) = ' ' then 1 end, 
              substr(str, 1, 20)
       from   inputs
     )
select str, 
       case flag when 1 then fragment 
                        else substr(fragment, 1, instr(fragment, ' ', -1) - 1) end
       as new_str
from   prep;

STR                                                     NEW_STR            
------------------------------------------------------- --------------------
Lorem ipsum dolor sit amet, consectetur adipiscing elit Lorem ipsum dolor   
Lorem ipsumdolor sit amet, consectetur adipiscing elit  Lorem ipsumdolor sit
Loremipsumdolorsitametconsedtetur                                           
Lorem ipsumdolorsit amet, consectetur etc.              Lorem ipsumdolorsit 
Lorem ipsum dolorsitamet, consectetur adipiscing elit   Lorem ipsum
abcdef ghijk lmno                                       abcdef ghijk lmno

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81970

If SQL Server and if you don't mind a UDF.

Declare @YourTable table (SomeText varchar(500))
Insert Into @YourTable values
('Lorem ipsum dolor sit amet, consectetur adipiscing elit.'),
('Lorem ipsumdolor sit amet, consectetur adipiscing elit'),
('Lorem ipsum dolorsitamet, consectetur adipiscing elit')

Declare @MaxLen int = 20
Select *,Trimmed = [dbo].[udf-Str-TrimToWord](SomeText,@MaxLen)
 From  @YourTable

Returns

SomeText                                                    Trimmed
Lorem ipsum dolor sit amet, consectetur adipiscing elit.    Lorem ipsum dolor
Lorem ipsumdolor sit amet, consectetur adipiscing elit      Lorem ipsumdolor
Lorem ipsum dolorsitamet, consectetur adipiscing elit       Lorem ipsum

The UDF

CREATE FUNCTION [dbo].[udf-Str-TrimToWord] (@String varchar(max),@MaxLen int)
Returns varchar(max)
AS
Begin
    Return LEFT(@String,@MaxLen-CharIndex(' ' ,Reverse(Left(@String,@MaxLen))))
End

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

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

Upvotes: 2

Related Questions