How to select a substring up to Nth space based on the 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:

Lorem ipsum dolor sit amet, consectetur adipiscing elit
Lorem ipsumdolor sit amet, consectetur adipiscing elit
Lorem ipsum dolorsitamet, consectetur adipiscing elit

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

Lorem ipsum dolor si
Lorem ipsumdolor sit
Lorem ipsum dolorsit

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

Lorem ipsum dolor
Lorem ipsumdolor sit
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

In Django, how do I create a template that queries my old SQL Server and Oracle databases and sends that to view?

I have a lot of old vbscript webpages using Classis ASP. Those ASP pages have lots of different databases queries to different databases all inside a given .asp file. I want to write those in Python and use Django as the framework. Will I be able to

SQL selection based on the maximum value of one column and the specific identifier of another

I am trying to return one value of a column based on the max value of another column and a specific id of a third column, all these in the same table. I tried to do the following sql, but it does not work: SELECT "value" FROM event_log JOIN (SEL

how to stamp with different spaces based on the length of the previous column in the ++ / vim / sed notebook

How to pad with different spaces based on the length of previous column in notepad++/vim/sed? For example: 123 456 1 456 233 555 232233 32323 => 123 456 1 456 233 555 232233 32323 Is it possible in notepad++/sed/vim?You can do it with vim: :%s:\v^(\d

How to select a substring in Oracle SQL up to a specific character?

Say I have a table column that has results like: ABC_blahblahblah DEFGH_moreblahblahblah IJKLMNOP_moremoremoremore I would like to be able to write a query that selects this column from said table, but only returns the substring up to the Underscore

Oracle SQL | How to select a substring where it starts with a certain letter and ends with a certain symbol?

If I had this: NAME EYES==ID==HAIR Jon Brown==F9182==Red May Blue==F10100==Brown Bill Hazel/Green==F123==Brown ...and I wanted to create a new ID column with the ID alone, and I know that everyone's ID starts with an 'F' and will end at the '=' how w

C # How to select a Hash code for a class that violates the Equals contract?

I've got multiple classes that, for certain reasons, do not follow the official Equals contract. In the overwritten GetHashCode() these classes simply return 0 so they can be used in a Hashmap. Some of these classes implement the same interface and t

How to select a column name with a space between mysql on linux / ubuntu

I am working on a project where another developer created table with column names like 'Business Name'. That is a space between two words. I am a Linux user now; If I run a SELECT statement with 'Business Name' it says there is no columns with name '

How to replace a substring that can have spaces between

In lua, I need to detect a substring which may contain spaces. For instance, I have a string local str = "Hel lo World" How do I replace "Hello" from this string? Do I need to use patterns? EDIT I came up with something like this local

How to select a file from an input html tag in the Selenium 2 grid with a remote driver

How to select a file from a input html tag in Selenium 2 gird with a remote driver? I am tring to test my webpage and it requires me to select a file. Here is a HTML snippet: <div class="form-group"> <label for="csvFile">CS

How to select rows from MySQL based on the maximum value of a column and grouping two other columns?

How can I select rows from MySQL based on max value of a one column and grouping two other columns? The query needs to group by code, group by element_id with where clause (element_id=1.B.1.b) and max(data_value). code element_id data_value 11-1011.0

How to select an option from a drop-down menu via the location of the list?

Is there a way in python with selenium that instead of selecting an option using a value or name from a drop down menu, that I can select an option via count? Like select option 1 and another example select option 2. This is because it's a possibilit

How to get the last week's range based on the current date in sql?

I have this code in crystal reports that gives me last week date range based on the current date. First day of the week: If DayOfWeek(currentdate) = 2 Then currentdate Else If DayOfWeek(currentdate) = 3 Then dateadd ("d",-1,currentdate) Else If

How do I get an average of one column based on the value of another column?

SQL Question I am currently trying to get the average of a certain subset of a column based on the shared value of another column. Here's some example data: Score Player 5 1 9 1 7 1 8 2 3 2 6 2 So how can I get the average of Player 1's scores separa

How to get a single row for each client based on the highest value in another column in SQL Server

I have a table that contains some customer information: -------------------------------------------- |CustID CustType OrderType CountofOrders| |------------------------------------------| | 1 C DD 23 | | 2 IC DE 10 | | 2 IC DR 7 | | 2 IC DS 7 | | 3 C

Related Articles