CODEWITHSUNDEEP
oracle-databaseoracle11g
arianit ax
arianit ax

Reputation: 307

Format string in Oracle

I'm building a string in oracle, where I get a number from a column and make it a 12 digit number with the LPad function, so the length of it is 12 now. Example: LPad(nProjectNr,12,'0') and I get 000123856812 (for example). Now I want to split this string in parts of 3 digit with a "\" as prefix, so that the result will look like this \000\123\856\812.

How can I archive this in a select statement, what function can accomplish this?

Upvotes: 2

Views: 9010

Answers (3)

user5683823
user5683823

Reputation:

It is much easier to do this using TO_CHAR(number) with the proper format model. Suppose we use \ as the thousands separator.... (alas we can't start a format model with a thousands separator - not allowed in TO_CHAR - so we still need to concatenate a \ to the left):

See also edit below

select 123856812 as n,
       '\' || to_char(123856812, 'FM000G000G000G000', 'nls_numeric_characters=.\') as str
from   dual
;

        N  STR
---------  ----------------
123856812  \000\123\856\812

Without the FM format model modifier, TO_CHAR will add a leading space (placeholder for the sign, plus or minus). FM means "shortest possible string representation consistent with the model provided" - that is, in this case, no leading space.

Edit - it just crossed my mind that we can exploit TO_CHAR() even further and not need to concatenate the first \. The thousands separator, G, may not be the first character of the string, but the currency symbol, placeholder L, can!

select 123856812 as n,
       to_char(123856812, 'FML000G000G000G000', 
                          'nls_numeric_characters=.\, nls_currency=\') as str
from   dual
;

Upvotes: 3

Aleksej
Aleksej

Reputation: 22949

Assuming strings of 12 digits, regexp_replace could be a way:

select regexp_replace('000123856812', '(.{3})', '\\\1') from dual

The regexp matches sequences of 3 characters and adds a \ as a prefix

Upvotes: 3

JeromeFr
JeromeFr

Reputation: 1928

SUBSTR returns a substring of a string passed as the first argument. You can specify where the substring starts and how many characters it should be.

Try

SELECT '\'||SUBSTR('000123856812', 1,3)||'\'||SUBSTR('000123856812', 4,3)||'\'||SUBSTR('000123856812', 7,3)||'\'||SUBSTR('000123856812', 10,3) FROM dual;

Upvotes: 1

Related Questions