# Code formatter SUBSTR expression problem

**URL:** <https://forums.toadworld.com/t/code-formatter-substr-expression-problem/40838>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [April 16, 2018, 12:26am UTC](https://forums.toadworld.com/t/code-formatter-substr-expression-problem/40838 "2018-04-16T00:26:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![cenkakbulut](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@cenkakbulut](https://forums.toadworld.com/u/cenkakbulut)\
**Post date:** [April 16, 2018, 12:26am UTC](https://forums.toadworld.com/t/code-formatter-substr-expression-problem/40838/1 "2018-04-16T00:26:10Z")

</div>

Hello I’m using Toad 12.12.0.39 and I have a problem about formatting SUBSTR expressions. I’ve tried many options on Code Formatter but no success at all.

For example, I have a query

**SELECT \* FROM invoicejournal cij, unbilledorders unb WHERE SUBSTR (NLS\_LOWER (cij.SALESID), 1, 43) = SUBSTR (NLS\_LOWER (unb.ORDERNUM), 1, 43) AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS\_LOWER (unb.dataareaid), 1, 7) AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS\_LOWER (‘201’), 1, 7)**

And when I format this code it looks like this:

**/\* Formatted on 16.04.2018 10:17:31 (QP5 v5.318) \*/**  
**SELECT \***  
**FROM invoicejournal cij, unbilledorders unb**  
**WHERE SUBSTR (NLS\_LOWER (cij.SALESID), 1, 43) =**  
**SUBSTR (NLS\_LOWER (unb.ORDERNUM), 1, 43)**  
**AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) =**  
**SUBSTR (NLS\_LOWER (unb.dataareaid), 1, 7)**  
**AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) =**  
**SUBSTR (NLS\_LOWER (‘201’), 1, 7)**

But i want it like this:

**SELECT \***  
**FROM invoicejournal cij, unbilledorders unb**  
**WHERE SUBSTR (NLS\_LOWER (cij.SALESID), 1, 43) = SUBSTR (NLS\_LOWER (unb.ORDERNUM), 1, 43)**  
**AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS\_LOWER (unb.dataareaid), 1, 7)**  
**AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS\_LOWER (‘201’), 1, 7)**

What am i missing here? How can I edit code formatter to work like that?

Thank you!

---

<div class="post-metadata">

**Author:** ![avergison](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/avergison/32/66_2.png) [@avergison](https://forums.toadworld.com/u/avergison)\
**Post date:** [April 20, 2018, 8:41am UTC](https://forums.toadworld.com/t/code-formatter-substr-expression-problem/40838/2 "2018-04-20T08:41:33Z")

</div>

Hello,

Perhaps the current right margin is too small to let each equation appear on a line by itself.

Using default options and when setting a margin of 87 or lower I’m getting

SELECT \*  
FROM invoicejournal cij, unbilledorders unb  
WHERE SUBSTR (NLS\_LOWER (cij.SALESID), 1, 43) =  
SUBSTR (NLS\_LOWER (unb.ORDERNUM), 1, 43)  
AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) =  
SUBSTR (NLS\_LOWER (unb.dataareaid), 1, 7)  
AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) =  
SUBSTR (NLS\_LOWER (‘201’), 1, 7)

As soon I my margin goes up to 97 I’m getting this:

SELECT \*  
FROM invoicejournal cij, unbilledorders unb  
WHERE SUBSTR (NLS\_LOWER (cij.SALESID), 1, 43) = SUBSTR (NLS\_LOWER (unb.ORDERNUM), 1, 43)  
AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS\_LOWER (unb.dataareaid), 1, 7)  
AND SUBSTR (NLS\_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS\_LOWER (‘201’), 1, 7)

As of margin 261 I’m even seeing the whole WHERE clause on a single line…

Or am I missing something?

Thanks,  
Andre

---

<div class="post-metadata">

**Author:** ![cenkakbulut](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@cenkakbulut](https://forums.toadworld.com/u/cenkakbulut)\
**Post date:** [April 23, 2018, 11:15pm UTC](https://forums.toadworld.com/t/code-formatter-substr-expression-problem/40838/3 "2018-04-23T23:15:02Z")

</div>

Thanks Andre,

I’ve never thought it’s all might be about spacing/margin issue.

It worked like a charm!

Have good day,

Cenk