0

I'm trying to limit the result of a column value to only the first line/phrase. I know there is an option like SUBSTRING(), but this is counting the characters, which does not do the wanted job. Is there a way to cut the data on the first line-break and only show that first sentence?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
George
  • 111
  • 1
  • 7

2 Answers2

1

Use SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX('First sentence.  Second sentence', '.', 1) AS output
FROM dual;

    output
1   First sentence

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • This is already getting really close, but if i'm not mistaking this will check when it is reaching the first "." in the info... The info in that column could have a "." in the first part of the sentence, but I don't want it to break at that time. isn't there something like "at line break, or at new line" that can be used? – George Jan 18 '18 at 09:21
  • Does your data even have newlines? Just because you see your text wrap does not mean there is actually a line break there. – Tim Biegeleisen Jan 18 '18 at 09:22
  • yes it does... users seem to insert all sorts of content, but I need to keep the possible allowed amount of entered text high.. it's used for something else also, but it just appears in an ugly way in the jqgrid overview.. so that's the place I want to limit it. – George Jan 18 '18 at 09:33
  • You never answered my question. Do line break characters appear in the text, and if so, do you want the first line, or do you want the first sentence? – Tim Biegeleisen Jan 18 '18 at 09:35
  • the line breaks are made by just pressing enter, there are no visual characters inside the test...I tried to put an example, but the formatting does not seem to work in comments in here :( – George Jan 18 '18 at 09:51
  • Then use `SUBSTRING_INDEX(col, '\n', 1)` – Tim Biegeleisen Jan 18 '18 at 09:53
0

I found a solutions that works like a charm for my problem, hope it will help someone else also ;) This is how I adapted the query:

 CONCAT(SUBSTRING(REPLACE(COLUMN,'\n',' '),1,75),'...') AS COLUMNNAME

Which results in getting the info from that column, removing new lines, sticking it all together with a space in between and then returning only the 75 first characters, followed by "..."

George
  • 111
  • 1
  • 7