OraFAQ Forum: SQL & PL/SQL » Adding spaces to the column value

Home » SQL & PL/SQL » SQL & PL/SQL » Adding spaces to the column value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Adding spaces to the column value [message #610221]

Tue, 18 March 2014 10:15

callmevj
Messages: 2
Registered: March 2014

Junior Member

Hi,
I have a value in a database table column and would like to add space after every 3 numbers.
eg:my value is 678555321 and now I need the value to be 678 555 321

Any help on this is appreciated.

Report message to a moderator

Re: Adding spaces to the column value [message #610223 is a reply to message #610221]

Tue, 18 March 2014 10:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

callmevj wrote on Tue, 18 March 2014 08:15

Hi,
I have a value in a database table column and would like to add space after every 3 numbers.
eg:my value is 678555321 and now I need the value to be 678 555 321

Any help on this is appreciated.

what datatype is the column holding these values?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Adding spaces to the column value [message #610225 is a reply to message #610221]

Tue, 18 March 2014 10:36

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

You can use REGEXP_REPLACE or TO_CHAR. There might be other ways too. Which is better depends on column datatype.

SY.

Report message to a moderator

Re: Adding spaces to the column value [message #610226 is a reply to message #610225]

Tue, 18 March 2014 10:46

_jum
Messages: 577
Registered: February 2008

Senior Member

As a first example for REGEXP_REPLACE:

SELECT REGEXP_REPLACE ('678555321', '([[:digit:]]{3})', '\1 ') repl 
  FROM DUAL;

REPL
-----------------------
678 555 321

You replace exactly 3 digits with itself (backreferenz \1) and a space.

[Updated on: Tue, 18 March 2014 10:47]

Report message to a moderator

Re: Adding spaces to the column value [message #610229 is a reply to message #610226]

Tue, 18 March 2014 11:10

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, and what if value is 67855532? Your code would replace is with 678 555 32. And most likely OP wants 67 855 532. Also, save your fingers use \d instead of

[[:digit:]]

.

SY.

[Updated on: Tue, 18 March 2014 11:11]

Report message to a moderator

Re: Adding spaces to the column value [message #610249 is a reply to message #610229]

Tue, 18 March 2014 13:28

callmevj
Messages: 2
Registered: March 2014

Junior Member

Thanks a lot guys for the response.

Report message to a moderator

Re: Adding spaces to the column value [message #610250 is a reply to message #610229]

Tue, 18 March 2014 13:37

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Solomon Yakobson wrote on Tue, 18 March 2014 17:10

Well, and what if value is 67855532? Your code would replace is with 678 555 32. And most likely OP wants 67 855 532. Also, save your fingers use \d instead of
[[:digit:]]
.

A question, how do you write with \d "a digit or some other things" like in:

SQL> SELECT REGEXP_REPLACE ('67A55B32C', '([[:digit:]ABC]{3})', '\1 ') repl FROM DUAL;
REPL
------------
67A 55B 32C

Report message to a moderator

Re: Adding spaces to the column value [message #610253 is a reply to message #610250]

Tue, 18 March 2014 14:43

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Tue, 18 March 2014 14:37

A question, how do you write with \d "a digit or some other things" like in

SELECT REGEXP_REPLACE('67A55B32C','((\d|[ABC]){3})','\1 ') repl FROM DUAL;

REPL
------------
67A 55B 32C

SCOTT@pdb1orcl12 >

Obviously the more granular it goes the less chance we can use regexp perl/posix extensions.

SY.

Report message to a moderator

Re: Adding spaces to the column value [message #610255 is a reply to message #610250]

Tue, 18 March 2014 14:51

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

And, btw, even if we count left to right, it should be right trimmed:

SELECT RTRIM(REGEXP_REPLACE('67A55B32C','((\d|[ABC]){3})','\1 ')) repl FROM DUAL
/

REPL
-----------
67A 55B 32C

SCOTT@pdb1orcl12 >

SY.

Report message to a moderator

Re: Adding spaces to the column value [message #610257 is a reply to message #610255]

Tue, 18 March 2014 15:01

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Ah! pipe, forgot this, thanks.

Report message to a moderator

Previous Topic:	Help with grouping documents
Next Topic:	Query taking Too much time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 03:49:10 CDT 2024