asked on Oracle: New line after 50 characters
Experts,
I am trying to structure my lengthy summary data in my TKT_SUMMARY field to not exceed 50 characters for each line. Which should evaluate if there is a space at the end of the 50 characters so that it creates a new line after complete words.
I read I can do something like this:
SUBSTR(TKT_SUMMARY,1,50)||
The problem is that it evaluates the first line only.
Any ideas?
I am trying to structure my lengthy summary data in my TKT_SUMMARY field to not exceed 50 characters for each line. Which should evaluate if there is a space at the end of the 50 characters so that it creates a new line after complete words.
I read I can do something like this:
SUBSTR(TKT_SUMMARY,1,50)||
The problem is that it evaluates the first line only.
Any ideas?
Oracle DatabaseSQL
Last Comment
Maliki Hassani
ASKER
I am displaying the data using ESRI's ArcMap. It's a data grid that shows ticket details for specific areas on the map. Currently the summary field extends out and doesn't wrap the text. I maybe able to get the admin to write the code in the application. Unfortunately, I don't have access to the server it lives on I can only manipulate the table structure of the data.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Okay great. I will keep looking as well.
I found something that might work, but it starts to get scary after 2 pieces. What is the maximum length of the field? Also, do you want to break at the space before character 50 or the first one after character 50?
If you want before, this will give you the first piece:
substr(tkt_summary, 1, instr(tkt_summary, ' ', 50-length(tkt_summary), 1))
Then this will give you the second piece:
substr(tkt_summary, instr(tkt_summary, ' ', 50-length(tkt_summary), 1)+1, instr(tkt_summary, ' ', instr(tkt_summary, ' ', 50-length(tkt_summary), 1)-length(tkt_summary), 1))
After that it starts to get pretty scary. I don't want to try to go further until I know if you are looking for space before or after 50 characters and how big the field is. If it is a 4000 character field, then the substr/instr approach isn't going to be feasible.
If you want before, this will give you the first piece:
substr(tkt_summary, 1, instr(tkt_summary, ' ', 50-length(tkt_summary), 1))
Then this will give you the second piece:
substr(tkt_summary, instr(tkt_summary, ' ', 50-length(tkt_summary), 1)+1, instr(tkt_summary, ' ', instr(tkt_summary, ' ', 50-length(tkt_summary), 1)-length(tkt_summary), 1))
After that it starts to get pretty scary. I don't want to try to go further until I know if you are looking for space before or after 50 characters and how big the field is. If it is a 4000 character field, then the substr/instr approach isn't going to be feasible.
ASKER
thanks, johnsone.
It's not a specific character really, it is just a way to make the field not look so long. So any idea that is easy to do , will work.
The summary field is set to max at 2000 characters.
Thanks
It's not a specific character really, it is just a way to make the field not look so long. So any idea that is easy to do , will work.
The summary field is set to max at 2000 characters.
Thanks
The query below works. The catch is that it is horribly inefficient. It will break the column up into parts no more than 100 characters long and break on a space. Since there are 2000 characters max, that means 20 columns. However since we break before 100 characters, it could mean 21 (or 22 but hopefully not). If anything comes back in the last TKT_SUMMARY column, then we went to 22 columns and have a problem. You will have to change the name of the table in the last line. And any WHERE clause that you want to add should go inside the inner most subquery.
SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
14,
15,
16,
17,
18,
19,
20,
Substr(tkt_summary, 1, CASE
WHEN Length(tkt_summary) <= 100 THEN 100
ELSE Instr(tkt_summary, ' ',
100 - Length(tkt_summary),
1)
END) p21,
CASE
WHEN Length(tkt_summary) <= 100 THEN NULL
ELSE Substr(tkt_summary, Instr(tkt_summary, ' ', 100-Length(tkt_summary
), 1)
+ 1)
END tkt_summary
FROM (SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
14,
15,
16,
17,
18,
19,
Substr(tkt_summary, 1, CASE
WHEN Length(tkt_summary) <= 100 THEN 100
ELSE Instr(tkt_summary, ' ',
100 - Length(tkt_summary),
1)
END) p20,
CASE
WHEN Length(tkt_summary) <= 100 THEN NULL
ELSE Substr(tkt_summary, Instr(tkt_summary, ' ', 100-Length(
tkt_summary), 1)
+ 1)
END tkt_summary
FROM (SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
14,
15,
16,
17,
18,
Substr(tkt_summary, 1, CASE
WHEN Length(tkt_summary) <= 100
THEN
100
ELSE Instr(tkt_summary, ' ',
100 - Length(tkt_summary),
1)
END) p19,
CASE
WHEN Length(tkt_summary) <= 100 THEN NULL
ELSE Substr(tkt_summary, Instr(tkt_summary, ' ', 100
-Length(
tkt_summary), 1)
+ 1)
END tkt_summary
FROM (SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
14,
15,
16,
17,
Substr(tkt_summary, 1, CASE
WHEN
Length(tkt_summary) <= 100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary),
1)
END) p18,
CASE
WHEN Length(tkt_summary) <= 100 THEN NULL
ELSE Substr(tkt_summary, Instr(tkt_summary, ' '
, 100
-Length(
tkt_summary), 1)
+ 1)
END tkt_summary
FROM (SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
14,
15,
16,
Substr(tkt_summary, 1, CASE
WHEN
Length(tkt_summary) <= 100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary),
1)
END) p17,
CASE
WHEN Length(tkt_summary) <= 100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ', 100
-Length(
tkt_summary), 1)
+ 1)
END tkt_summary
FROM (SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
14,
15,
Substr(tkt_summary, 1, CASE
WHEN
Length(tkt_summary) <= 100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary),
1)
END) p16,
CASE
WHEN Length(tkt_summary) <= 100
THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM (SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
14,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary)
,
1)
END) p15,
CASE
WHEN Length(tkt_summary
) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
13,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p14,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
p12,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p13,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
p11,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p12,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p10,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p11,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
p9,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p10,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
p8,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p9,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
p7,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p8,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
p6,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p7,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
p5,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p6,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
p4,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p5,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
p3,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p4,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
p2,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p3,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT p1,
Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p2,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary,
Instr(tkt_summary, ' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM
(SELECT Substr(tkt_summary, 1,
CASE
WHEN
Length(tkt_summary) <=
100
THEN
100
ELSE
Instr(tkt_summary, ' ',
100 - Length(tkt_summary
),
1)
END) p1,
CASE
WHEN Length(
tkt_summary) <=
100 THEN
NULL
ELSE Substr(tkt_summary
,
Instr(tkt_summary,
' ',
100
-Length(
tkt_summary), 1)
+ 1)
END
tkt_summary
FROM ticket)))
)))))))
))))))))));
If you are actually going to consider the method that I posted, I highly recommend that you change it to be 200 characters and cut the number of columns back to 11. That makes the inefficiency at least bearable.
To make the change, all you have to do is change 100 to 200 everywhere, then take selects off the front and parenthesis off the end.
To make the change, all you have to do is change 100 to 200 everywhere, then take selects off the front and parenthesis off the end.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
great that's awesome yeah that's a lot of coding but it seems to be the only way that it can work I will test this code out when I return tomorrow thank you for your assistance
>>but it seems to be the only way that it can work
Probably not the only way. If can probably be much more efficient if you can create a function in the database.
Probably not the only way. If can probably be much more efficient if you can create a function in the database.
I was trying to do a purely SQL way. As I said, it is horribly inefficient. I wasn't sure if a function was a viable solution. Sometimes adding functions isn't allowed.
You could turn the same basic logic into a function. That should be much more efficient.
You could turn the same basic logic into a function. That should be much more efficient.
ASKER
Yes, i could add as a function. Udf?
Not extensively tested, but here is a function:
I see that there was another function already posted, but I couldn't follow it. The for loops with fixed iterations didn't seem to make sense to me. This way seems simpler to me. Keep iterating through the string and breaking it apart.
To call it, just use it in a select statement like this:
select str_break(tkt_summary) from mytab;
Definitely much more efficient than the recursive SQL way.
CREATE OR replace FUNCTION Str_break (v_str VARCHAR2)
RETURN VARCHAR2
AS
v_breakstr VARCHAR2(2000);
v_return VARCHAR2(4000) := NULL;
v_maxlen NUMBER := 50;
v_breakpos NUMBER;
BEGIN
v_breakstr := v_str;
LOOP
IF Length(v_breakstr) <= 50 THEN
IF v_return IS NULL THEN
v_return := v_breakstr;
ELSE
v_return := v_return
|| Chr(10)
|| v_breakstr;
END IF;
exit;
END IF;
SELECT Instr(v_breakstr, ' ', v_maxlen - Length(v_breakstr))
INTO v_breakpos
FROM dual;
IF v_breakpos = 0 THEN
v_breakpos := Length(v_breakstr);
END IF;
IF v_return IS NOT NULL THEN
v_return := v_return
|| Chr(10);
END IF;
v_return := v_return
|| Substr(v_breakstr, 1, v_breakpos);
v_breakstr := Substr(v_breakstr, v_breakpos + 1);
END LOOP;
RETURN( v_return );
END;
/
I see that there was another function already posted, but I couldn't follow it. The for loops with fixed iterations didn't seem to make sense to me. This way seems simpler to me. Keep iterating through the string and breaking it apart.
To call it, just use it in a select statement like this:
select str_break(tkt_summary) from mytab;
Definitely much more efficient than the recursive SQL way.
ASKER
Thank you for taking the time to do this. I will run it tomorrow morning!
@johnsone : the only sense in the fixed iterations is to avoid a loop: as long as not fully tested i rather get a wrong result then no result at all.
@flow01: Not saying what you posted is wrong. I had trouble following it. It may work just fine.
ASKER
> Script lines: 1-3 --------------------------
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "BENCO.STR_BREAK", line 33
Script line 1, statement line 1, column 7
Any idea why this error is occurring?
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "BENCO.STR_BREAK", line 33
Script line 1, statement line 1, column 7
Any idea why this error is occurring?
ASKER
Seems to work when a run the query for a small amount of records. When my where clause is pulling a 100+ tickets it errors. Still reviewing.
Number of records shouldn't matter. It is one particular record that is causing the issue. Bump up the size of v_breakstr to 4000.
ASKER
Yeah, I think that is the issue.. Narrowing down the record that is causing this. thanks
Actually, based on where the error is, I don't think that is the issue. I think it is an issue where you have a "word" that is greater than 50 characters. Change this:
IF v_breakpos = 0 THEN
v_breakpos := Length(v_breakstr);
END IF;
IF v_breakpos = 0 THEN
v_breakpos := v_maxlen;
END IF;
ASKER
So that fixed the error message, but now it is taking a long time to run which seems like an endless loop. Trying to find the record that is causing this again.
ASKER
Issue is that there is a null in the summary
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Bingo got it to work!
ASKER
Thanks everyone for the assistance!
Can you not control the text wrapping at display time?
What application is displaying the data?