asked on

Add a 999 to the front of a integer (sql code)

Hello, What I want to do is add a 999 to the front of a integer value in sql. Basically, lets say I have a value of 1234. I want to in sql code add a 999 to it so it would look like 9991234. Right now my code is literally adding 999 to 1234 giving me a value of 2233 which is not what I am after.

Any help would be greatly appreciated!

Daniel Wilson

You need to convert to a string to do the concatenation, then convert back to an integer.

Looking up CAST or similar ofr Oracle now ...

Daniel Wilson

http://www.techonthenet.com/oracle/functions/cast.php

Select Cast('999' + Cast(MyIntegerField as varchar2(6)) as integer) as MyFinishedInteger
From MyTable

Open in new window

jwebster77

ASKER

Here is what I have now but it is still doing it. THoughts?

UPDATE PO.PO_LINES_ALL SET PO.PO_LINES_ALL.PO_HEADER_ID = Cast('999' + CAST(PO.PO_LINES_ALL.PO_HEADER_ID As char(10)) as integer), PO.PO_LINES_ALL.LINE_NUM = CAST('999' + Cast(PO.PO_LINES_ALL.LINE_NUM As char(10)) as Integer) WHERE PO_HEADER_ID = 34735 AND LINE_NUM = 13

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

Nay, you don't need a cast. Just use the string concatenation operator, which implicitely converts numbers to strings:
select '999' || IntegerField from Tbl

awking00

Obtaining a numeric value with an explicit conversion would be -
select to_number('999'||to_char(yourintegerfield))

Mark Wills

Might want to keep the trailing part of the number formatted correctly so you dont trip over differing numbers....

e.g. if you number was 1 you may prefer to end up with 99900001 rather than 9991

select '999'||to_char(yourno,'FM00000') from yourtable

Daniel Wilson

JWebster,

You have solutions from 4 of us now.

Did any of them work for you?