Cutting numbers in pl/sql

Question

1.00/5 (1 vote)

See more:

Guys I have 1 question i have 1 column where i have numbers like 995551808080
only change is after 95551 and i want to Cut all unececary Numbers from there and i want to be there only 995551 << this number can you help me with some script?

Posted 17-Aug-15 2:04am

GTR0123

Updated 17-Aug-15 2:11am

Dave Kreskowiak

v2

Add a Solution

Comments

ZurdoDev 17-Aug-15 8:20am

Doesn't Oracle have a LEFT() function or something like that?

4 solutions

Solution 3

In MSSql

Sol 1: - Select Left ('995551121212',6)
Output :- 995551

Use Substring Fuinction :-

Sol 2 :- Select Substring ('995551121212',1,6)
OutPut :- 995551

Posted 17-Aug-15 23:48pm

KapilMuni

Solution 2

Now i Search and Found substr << function that cut numbers how you want

Posted 17-Aug-15 3:28am

GTR0123

Comments

Jörgen Andersson 19-Aug-15 9:48am

SUBSTR() is correct, I don't see why you got downvoted. Have a compensation.

Maciej Los 19-Aug-15 15:17pm

Agree!

Solution 4

if your column type is varchar or nvarchar use subtring like
SELECT SUBSTR('995551808080', 1, 6);

if it is BIGINT or INT

firstly cast it to varchar like

SELECT CONVERT(varchar(10), field_name) FROM table_name

and than use SUBSTR

Posted 20-Aug-15 1:45am

ayhanUGUR

Comments

Jörgen Andersson 21-Aug-15 2:53am

Not necessary, conversion is implicit.
Bigint is btw not a datatype in Oracle. You're mixing it up with SQLServer.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-08-17T03:01:00

Solution 1

It's very easy!

SQL

SELECT SUBSTR('995551808080', 1, 6);
        -- returns: '995551'

Documentation: SUBSTR[^]

Posted 17-Aug-15 3:01am

Maciej Los

Updated 19-Aug-15 9:16am

v2

Comments

Jörgen Andersson 19-Aug-15 9:47am

Nope that doesn't work, your link is for MySQL, nowadays owned by Oracle.
For Oracles own implementation you need to use SUBSTR().

Maciej Los 19-Aug-15 15:17pm

Thank you, Jorgen for your valuable comment. The answer has been updated.

Cutting numbers in pl/sql

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0

Cutting numbers in pl/sql

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us