Oracle 10g: truncate string

Question

I'm having a query that append a string value from multiple rows and insert the result in a new table.

I would like to limit the String length that is inserted.

Question

What is the best way to achieve that : Should I use SUBSTR or SUBSTRB or is there a better way ?

Define the column to have a fixed limit, such as varchar2(10) or char(10). — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2018 at 11:05
You still need to truncate your aggregated string so it fits into the size-restricted column in the table though, to avoid run-time errors; and whether you should use substr or substrb depends on whether that size is restricted in characters or bytes (i.e. varchar2(10 char) or varchar2(10 byte)). — Alex Poole
– Alex Poole, Commented Aug 29, 2018 at 12:10
How are you building the string? If you are appending to your string in a loop, I would stop the iteration when the max size is reached, instead of building a long string and then truncating it. — Aleksej
– Aleksej, Commented Aug 29, 2018 at 12:17
@Aleksej you absolutly right on that. The string is built on the fly as follow: stackoverflow.com/questions/52040922/…. Ideally, I would have avoid unecessary append, but it will change completely the query. I guess there are no turn-around beside break the query in fragements ? — Hey StackExchange
– Hey StackExchange, Commented Aug 29, 2018 at 13:41
@AlexPoole That the problem I wanted to avoid. Is SUBSTR the way to go ? (at the cost of building a string for nothing (above the limit) ? — Hey StackExchange
– Hey StackExchange, Commented Aug 29, 2018 at 13:43

Jacob · Accepted Answer · 2018-08-29 11:32:55Z

7

Using SQL you can use SUBSTR function to get the character limit

SELECT SUBSTR (test_string, 0, 10) FROM table_name

E.g.

SELECT  SUBSTR ('Testing1/testing2', 0, 10) FROM dual

Using LENGTH function, you will be able to ascertain the count of the string

SELECT  LENGTH ('Testing1/testing2') FROM dual

answered Aug 29, 2018 at 11:32

Jacob

14.8k69 gold badges215 silver badges323 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

In case anyone else is wondering, yes, 0 is treated as 1 for the start index.